Just for fun, I thought we could try to propose how we would make a scale that would show the full spectrum of adventure games, from the ones that make total sense, to the ones that have puzzles designed by Arkham inmates.

I was thinking about something like this:
Spock's game- all the puzzles make total logical sense, and can be figured out without any outside help (example: 1954 Alcatraz, Memoria, A New Beginning)

Hamlet's game- all the puzzles make logical sense, but it can be a very peculiar, twisted logic, that fits the game's peculiar world. Still, they do make sense, and can be all figured out without any outside help, provided you "get" this peculiar logic. (example: Deponia 2 & 3, Ben There Dan That)

Houston, we have a problem - most puzzles in the game make logical sense, but once in a while you'll come across a puzzle that defies logic, and can only be solved with outside help, or luck of randomly trying everything. (example: Grim Fandango, Night of the Rabbit)

Dr Jekyll's game - the logical and nonsensical puzzles are almost 50/50. Finishing the game without any outside help may be impossible without patience to try pretty much everything to solve some puzzles. (example: Sam & Max Save the World, The Dig)

Necronomicon- the game is totally insane, and will drive you insane too if you try to play the whole thing. The puzzles defy all logic even in retrospect, the amount of locations, items and characters make random attempts at solution almost impossible, and to finish the game without outisde help would be to see the face of god - a cruel, mad, deranged god. (example: Discworld)

I'm sure every adventure game player will have his own suggestions, dictate by individual experience with particularly devilish puzzles.

This can actually be applied to any game with adventure elements. For example:

Wizardry 4: Probably the third level of insanity.

La-Mulana: Would probably be in the fourth of even fifth level of insanity. (Yes, I have just said that the game is, puzzle-wise, harder that Wizardry 4.)

From what I saw of the La-Mulana 2 Kickstarter demo (from a video), it appears to fit the first category, as the developers seemed to get a better handle on how to make solvable riddles. (It still doesn't hold your hand; maybe more categories at the easy side of the scale are needed.)

One other important thing you forgot to mention; how common are "dead man walking" situations (where the game is unwinnable, but you can continue playing). Wizardry 4 has one that can happen, but the game fortunately warns you before the point of no return. La-Mulana does not, though it does have permanent missables, but none are required to beat the game.

Well, the scale I presented is not really about difficulty, but how logical or ill-logical the puzzles are. Logical, sensible puzzles can still be very difficult (like some puzzles in Star Trek 25th Anniversary or some othe Frogwares Sherlock Holmes games), and vice versa - in Samorost 2 or Botanicula (I'd say they are lvl 3 of my scale) it's still very easy to just stumble on the correct solution by clicking around.

To be fair, if you look at them objectively, almost all games are absolutely insane in their puzzles.
To take a random example of a game I very much like, a "serious" classic game which didn't intentionally have wacky puzzles, Indiana Jones and the Fate of Atlantis.

Okay, so you're in this underground ancient catacomb and you need to cross this deep chasm, so you pick up a ladder from the entrance, put it in your pocket, and use it to cross the chasm. In this area you will randomly find a statue holding a stone cup. You take the stone cup. Fair enough. You find another statue which has the head of a fish, you take the head with you. Weird, but okay.
Okay, now, after exploring some more, you find a room full of lava. It also has a part with a socket of some kind. Obviously, you plug in the fish head into the socket, and you get a stream of lava! You use the cup with the stream, and now you have a stone mug full of lava!
After some more exploring (or backtracking if you had found it before), you find a room with this huge bit of machinery, you attach a wheel to it, and pour the lava into a funnel at the top, and you get a bunch of beads.
You feed one of these beads into a dormant robot in yet another room, it comes alive, crushes a nazi and allows you to get to your partner.

Now, if I wasn't an adventure gamer, I'd find the whole thing incredibly bizarre. As an adventure gamer, it makes total sense to me :D.

This needs its own gog mix by someone very knowledgeable. My wife and I (but mostly the wife) love adventure games, but illogical puzzles ruin them.

I do hope fans of different kinds of adventure games might contribute their point of view. I love andventure games, but only the classic point&click stuff. I pretty much never play first person adventures like Myst, or any of the "walking simulators". And there are the Sierra style point & clicks (as opposed to the LucasArts-style) which I never played much.

Has anyone ever played Treasure Quest?

It's a point and click style, like Myst, from the mid 1990s. It was also part of a real life contest where if someone completed the game and had all the solutions to the puzzles, they could enter a contest where they could win $1,000,000. As a result the game is borderline nonsensical. Here's a review.

I guess the most famous insane puzzle is the motorcycle one from GK3?

I have a pretty large adventure game background and might be able to reasonably cover it, but...
People actually look at GOGmixes? What puzzle was that you encountered in Grim Fandango? I was able to reasonably solve it without getting any outside help or randomly throwing things together. Granted, some of the puzzles took me a little while to solve, but once I got a lead, I was able to figure it out.

As much as I want to contribute to this thread, it's a little difficult to quite stick games into categories like the ones you've presented. Excluding the extreme cases, it all depends on the individual approaching the game and how much patience they have before looking up a walkthrough.

I'd have to agree with you. Gabriel Knight 3 is actually a great game with some really good puzzles, but that one is just mad.

The thing with the sign at the crossroads early on for example. It's a really out there idea, and up to that point the game actually pretty much works on a rather down-to-earth logic, despite the setting. So the idea that the sign will work like a compass... ugh. Not in a million years would I have fiugred it out. And even then, the puzzle itself is not very well made. There was some other thing in the second act, but I can't remember now exactly, I think it was with the waiter and getting him lcoked up. It was one of those where you know what you're supposed to do, but the chain of how to get there is quite baffling.

True, to an extent, but in some games after you get the answer, whether from a walkthrough or through sheer patiance of trying every thing or finally figuring it out, you'll go "oh, damn, I should have thought of it sooner" while in others even in retrospect the answer makes no goddamn sense. Like I said, it's not about difficulty (a subjective thing anyway), but rather how sensible/absurd the puzzles are.