Let's start, I suppose, by offering up a couple of definitions and examples of "weird" with regards to fiction, because the broad speculative fiction mega-genre includes within its legion ranks magic talking animals, tentacled aliens, and people who turn into wolves, and we take all these things as fairly prosaic.
There is weird fiction that's weird by virtue of a wholey novel or improbable premise. One of my favorite novels, Snow Crash, opens with a half-korean half-african american hacker who has, in the course of his employment with a mafia-run pizza delivery service, come to realize people are far more intimidated when he carries a sword than when he uses his standard issue gun. It's like a Chomsky sentence; all the parts are gramatically correct, but it still takes you a moment to parse because you're not used to the combination. One of my other favorite novels, Even Cowgirls Get the Blues, opens with a discussion several pages long about the relative anal temperature of a couple of animals, notably the oyster and the hummingbird, and the philosophical conclusion that while hummingbirds can generally only make a tiny, wet, white mess, out of the anus of the much cooler oyster, you get a pearl. The larger book is about a woman with freakishly giant thumbs who becomes the world's best hitchhiker, people who worship a giant clock, and a dude ranch to promote vaginal douche products.
That doesn't get any less delightfully freakish when you stretch it out across a whole book, and it's a champion level of weird I don't get anywhere near approaching.
Part of the reason I was thinking about it was having just listened to The Things by Peter Watts (link), which I think expands well beyond what must have been its original aim to tell an old sci-fi horror story from the monster's point of view. A large part of the weirdness here is the outsider looking in.
So here are my guesses about the inherent appeal of weird.
In the first part, completely strange and novel experiences tend to leave a stronger impression. I think when something meets our expectations we have a tendency just to skim over all the parts we already intrinsically understand and just look at the few differences. But when the whole thing is different you're forced to look at and to examine the whole thing just to try to make sense of it. Weird requires far more engagement to comprehend, and when there's something there behind it, I think it makes for some of the most rewarding reading (the example that comes to mind for me is the end of "Canticle for Lebowitz" which is weird and horrifying and tragic and transcendent, certainly for the viewpoint character, but I think for the reader as well).
On the other hand, I've read stories that were intensely weird, but when you unwrapped them, they didn't seem to be saying anything-or worse, they seemed to be saying something utterly trite- and I've found these to be generally disappointing. Though again, something like Jay Lake's "Clown Eggs" really stands up for me, so perhaps the larger issue is that the stories I don't care for stand up as weird, but fail to be a story so much as a chronologically ordered series of events. Possibly chronologically.
Novelty can be a very beautiful thing, that only really works the first time you see a thing- not that you can't enjoy it later, just that at that point it's no longer a surprise. There's a lot of play involved in a good bit of weirdness. Much of it occurs seemingly for its own sake- oh certainly it functions to create an atmosphere within the piece of distortion, of unreality, of a space where anything could happen, but I know when I'm writing something off, or reading it in some of the people whose weirdness I like best, there just seems to be a joyfulness about figuring out just how strange a thing you can ram into a story and have it hold, an electric sort of flirtation with disaster.
There's the lure of the exotic: things so outside our ordinary that they don't exist in a realm of sense. We can make ourselves tourists on the other side of the looking glass.
I also think, for me at least, there is in part some element of feeling like a weird outsider myself at times, and coming to identify with being outside the norm. That's hardly an unusual feeling, but I think there might be something to the idea that one might glom on to stories that seem unique, outside the main stream, subversive, or what have you, because that's something one likes about oneself.
Speaking of subversive, there is a school of thought that surrealism and weirdness works to undermine a rigid dominant, logical, or categorical paradigm, which I guess is true, but I don't think that's generally the appeal in so many words. Then again, if you say the same thing in non-academic speak; that weirdness gives one a chance to play around outside the expectations of every day life, it sounds about right, but more true because it has the emphasis on playing rather than sterile deconstruction.
And I guess when it comes down to it, that whimsical playfulness, even in the dark, somber surrealism, is what I love about weird.
(edit: I apologize for the rambling above. I wrote it all quite late at night)