Search code examples
sqljoinsubquerywhere-clauseh2

Subquery returns unexpected results with WHERE, but works fine with JOIN

I want to get random quotes from db, I'm using this query:

SELECT * FROM quote WHERE quote.id = (SELECT id FROM quote ORDER BY RAND() LIMIT 1)

but I didn't get result time to time. Seems like subquery returns id that is not exists.

Subquery is working fine separately. And I didn't get id that is not exists.

And this query with JOIN instead WHERE works fine too.

SELECT * FROM quote JOIN (SELECT id FROM quote ORDER BY RAND() LIMIT 1) q1 ON quote.id = q1.id

Note: I can't use just subquery that returns whole quote, because I need to use some addition JOINs in main query. With these JOINs and without - result is the same.

Solution

  • There is a big difference between a subquery in the WHERE criteria of the first query and a derived table from table expression of the second query. They only look the same.

    1. RAND() function is non-deterministic, non-deterministic functions may return different results for the same arguments. Usage of this function in a subquery makes the whole subquery non-deteministic. Non-deterministic subqueries are executed every time when their values are needed and here they are actually needed multiple times.

      When outer query reads a row, it needs to verify that it matches the WHERE criteria, but non-deterministic subquery returns random results and this check will fail if id of checked row is different from random id returned from a subquery.

      If id column is indexed (most likely it is), the subquery is executed first time to create an index condition and second time to check the WHERE criteria, if it isn't, the subquery is executed for each row from the first table to check the same WHERE criteria, in either case the outer query may return no results.

    2. When the second query (with a join) is executed, its table value expressions, including that derived table, are constructed first, before any other steps of query execution. So there is only one execution of SELECT id FROM quote ORDER BY RAND() LIMIT 1, a derived table produced by this inner query isn't modified after that, so everything works as you expect.