SQLite Forum

Bad query results involving random()
Er, wow, alright. My mental model of CTEs and subqueries in FROM is that they semantically behave as if the result set of those queries is materialized - even if the implementation may optimize that.

This behavior definitely surprised me, and also implies that filtering on random() is impossible save for an explicit materialization step. Another interesting implication is that the expression `x <> x` can (and indeed does) evaluate to true.

If you're correct (and who am I to say you're not - I'm in no way qualified to argue the semantics of SQL), I suppose it's time to update my mental model...