"There are two kinds of questions: Puzzles, and Mysteries." - Noam Chomsky
There are two types of investigation story. There are those which how an apparently inexplicable event gets explained, and those which describe an inexplicable event.
On the one hand, stories where something unexplained happens - a murder in a locked room, someone being in two places at once, escape from an inescapable prison - and we know in advance that we'll get an explanation at the end, and thus the reassurance that the world really does make sense. Indeed, the whole point of the pretence that maybe it doesn't...is to enable the assertion that it does. Rationalist storytelling.
On the other hand, stories where the pretence is that maybe the world does make sense, enabling the reveal that it doesn't. Irrationalist storytelling.
In the latter, we enjoy not knowing. In the former, we enjoy finding out. In the one, stories about ghosts, miracles, demiurges, prophecies, and messages from the afterlife - irrationalism. In the other, crimes being solved, questions being answered, order being restored, chaos being defeated, and people being enlightened.
Magic, and science. The hidden, and the unhiding. The unknowable, and the coming to know.
Scooby Doo mysteries look like ghost stories, but there's always a non-supernatural explanation. Gregory House may not save the patient, but he always solves the mystery - and for his one unsolvable case, the unsolvability was the clue that the whole thing was a hallucination. The science in Dr Who may be junk, but even in the most supernatural stories, there is always an explanation and it's always presented as scientific.
Contrariwise, the Psychic Investigators may investigate, but the only explanation they're allowed to come up with is "something mysterious causes something mysterious to happen, mysteriously". Creationism explains one mystery in terms of another - one for which we're not allowed to ask an explanation.
So, is the dichotomy as simple as that? Let's look at some examples - including examples of stories that pose as one, but are the other.
Patrick McGoohan's The Prisoner was a surrealist espionage serial - which really ought to be a contradiction in terms. Surely the point of surreal events is to be unexplained and by implication inexplicable, while the point of an espionage story - like its close cousin the murder mystery - is to restore order and rationality by solving a puzzle.
But no - in The Prisoner the surrealism is window dressing, including the gainax ending. It's an espionage serial and thus promises a resolution - which McGoohan was unable to provide. If the genre had been surrealism with espionage decorations, fans wouldn't be trying to make sense of the willfully unreadable final episode - or be outraged by its unreadability.
The X-Files was a loose bundle of unexplained events, including the central mysteries of the alien plan, the conspiracy and Mulder's sister. The pairing of the scientific skeptic and mystical believer - with the believer being proven right every week - would seem to point to the series being a compilation of ghost stories, with no rational explanation offered or expected. But the show had a rationalist premise, with the promise of a coherent explanation being always held tantalisingly in the future.
When fans realised there was no explanation in Chris Carter's mind, they stopped watching.
At first glance, Lost looks similar. A massively growing heap of mysteries waiting to be explained in the finale...which in the event explained nothing and even contradicted the mythology is was supposed to make clear. But actually I think the fans were in on the deal from the start - the writers admitted to making it up as they went along, and the fans found it fun (for a while at least) to play along with the pretense that all would be explained.
In the comicbooks, Superman constantly gets new powers (and forgets about old ones) whenever the plot demands. But there's still the pretense that "Magic A is Magic A" - that there is one set of rules in the comicbook's universe, they're mutually consistant, and they're always been the same. So the metaphysics of the Superman universe is rationalistic - just badly thought out.
One episode of The Sarah Jane Adventures features a creepy morphing clown who steals children. Sarah Jane is sure she can use reason to understand him and technology to defeat him, but he taunts her with the suggestion that there simply is nothing to understand. Then it's explained that he's an alien that feeds on fear...and for me at least suddenly shrinks from an intriguing threat to another monster of the week.
Stanislaw Lem's novel "The Investigation" concerns a police investigation into a spate of corpses seemingly re-animating, moving around for a few hours, then re-de-animating. A consulting scientist finds some correlations between the incidents and some weather patterns, and proudly declares the mystery solved - the weather brought people back from the dead, somehow.
The detective fails to find a rational explanation, and is eventually persuaded to pin the blame on a scapegoat - a man who drove near most of the incidents, and who presumably faked the reanimations for unknown reasons and by unknown means. The detective's boss understands clearly that the purpose of the investigation is not to find the truth or serve justice, but to cobble together a plausible explanation which can be sold as both, so we can stop being disturbed by the inexplicable.
He says of the mythical criminal: "You'll get the bastard even if he doesn't exist".
In The Investigation the only one who can see the obvious truth - that something supernatural has occurred - is the reader. Lem's other "detective" novel was The Chain of Chance, but here there's nothing magical - just a cluster of coincidences which give the impression of a crime where none occurred.
David Cronenberg's eXistenz is built around a refusal the answer the question "Are we still in the game?", ending with a pile-up of contradictory clues and the characters unable to decide. But if Cronenberg intended to suggest that the question is undecidable even in principle, that's not what he was able to make his work do. eXistenz may intend to say "there is no truth", but only manages to say "we can't know the truth...so far".
Antinioni's film Blow Up concerns a young man who might have accidentally photographed a murder. But the photographic grain is ambiguous, and even though he finds a body, it later disappears. The point seems to be that all evidence is by nature inconclusive, and life in general is a series of unconnected events with no plot. Hence the multiple subplots that go nowhere.
William Friedkin's Cruising is superficially similar, but has a more radical inexplicability. On the surface, it tells of an undercover police investigation into three murders, culminating in the hero catching the killer. But it's shown that the police are only interested in closing the file, not stopping the murders, or finding the killer, or even just solving a mystery.
In the novel on which the film is based, Burns the protagonist gets away with a fourth murder offscreen himself, but in the film he may or may not have done it. In the book, Richards the killer commits the other three murders, but in the film we only know the police want to clear the case by blaming him. Indeed, they offer him a deal - confess to these and a slew of other murders...and you'll get a lighter sentence.
We hear the killer speak three times, and each time the killer is played by a different actor - and when we see his face after the third murder, it isn't Richards - it's the face of the first victim.
The close resemblance between Burns, and the killer(s), and the first three victims, and a number of incidental characters is a plot point. Richards hallucinates his dead father, but whoever commits the three onscreen murders, they all speak with the father's voice - and Friedkin goes out of his way to make it obvious the voice is dubbed.
Cruising is a supernatural movie masquerading as a police procedural. The fan theory that Burns is himself (unknowingly?) the sole killer can't be squared with the clues presented - but the point is, nothing can.
So, the dichotomy looks to hold up - with some complications. It's another question as to whether all stories could in principle be categorised this way, or whether the Rationalist/Irrationalist distinction is a product of our Age of Reason.
Does Beowulf look like a ghost story to us, simply because we've grown up expecting an explanation? Is the Epic of Gilgamesh actually intended to make sense?