The more important thing to me is the kind of player than the kind of character the player plays. A bad player can make a character of any alignment ruin the game for everyone. A good player can make a character of any alignment add to everyone's enjoyment of the game. If you want to play CE, you need a reason for why your character is working with other party members. You can be CE without being completely mad or you can be completely mad in a way that favors support of the group. Some of the biggest monsters in history loved their families and were very loyal to their friends. Instead of bonds of love, perhaps there are magical bonds that force you to support the party. Maybe you just really enjoy the adventuring and the party is your way to get opportuniteis to kills things and get loot. Whatever the character's motivations and contraints are, it is up to the player to come up with something that makes the character a workable member of the party.
As for whether I would want a CN character. Sure, if it is the right character played by the right player. The best real-world example of a CN person I can think of is Jerry Garcia or the Grateful Dead. Man, I love Jerry's music and he could be a very charismatic and loving person. He could also be a selfish prick, especially to the women in his life. More interesting is how he reacted to issues the band had with its success. Both cool and not so cool reactions.
CN Cool: Not only did he have no interest in policing people recording their shows and selling unlicensed merchandise, the Dead were known for allowing and in some cases setting up areas for people to bootleg their concerts, which is why you can support entire radio stations of just Dead music and not hear it all.
CN Cool: To hell with playlists and even playing the same songs the same way. The Dead were adept and Jerry a master at improvisation. Every concert was different. Song devolves into chaos and are new songs built up from the chaos.
CN Not Cool: The Hell's Angels liked to hang out with the Dead. I'm sure they provided some of the Dead's drugs and occasionally provided security. When some of the other band members raised concerns, especially for female staff, significant others, and groupies, Jerry's respons was "Well, you know, I don’t think that good means very much without evil”.
CN Not So Cool: When Dead's popularity had a resurgence in the 80s with their only hit single "Touch of Grey" (from their In the Dark Album), the traditional laid back community of fans that surrounded the Grateful Dead were swelled with touch heads and the huge groups of people coming into communities camping out, doing drugs, trying to sneak into venues were leading to issues with local communities and their police, plus fans were getting hurt and arrested. Most of the band members worked with local oranizers and law enforcement, making recordings to be played to fans asking them to do and not do certain things to be more safe and more respectful of local communities. Jerry refused to participate. He didn't want to tell people what to do.
CN Not Cool: Jerry (and other band members) would slip acid into peoples drinks and dose them without their consent. E.g., on a TV appearance, they didn't like the staged-party feel, so they dosed the drinks of the extras with LSD to turn the show from an artificial party into an authentic party.
Still, despite occasional fights and interventions, Jerry was very loyal to his bandmates and fans. He wanted to leave the band in the 90s but so many people's livelihoods depended on him, he stuck with it and felt trapped by it and abandoned a relationship over it. Which sounds more lawful than chaotic, but people are complex. Characters can be complex as well. One can have a particular view of the world and try to aspire to and live by ideals, but behave very differently from those ideals in specific areas of your life.