ernie/squeel — Issue #96: 'exists' subquery can not be correlated, also 'SELECT *' vs. 'SELECT 1'
Author: FOMNick
Created: 2012-01-12 21:08:19+00:00
State: open

Here is an example showing that correlated subqueries using 'exists' are not working quite as they should be: Say we have a 'users' table with a 'sign_in_count' field and we want to select only users that have signed in more than 10 times. Given:

User.where{exists(User.where{sign_in_count > 10})}

We should expect the following SQL to be generated (or something like it):

SELECT *
FROM users
WHERE EXISTS (
SELECT 1
FROM users users_inner
WHERE users_inner.id = users.id AND users_inner.sign_in_count > 10
)

But instead, we get (perenthesis nesting omitted):

SELECT *
FROM users
WHERE EXISTS (
SELECT *
FROM users
WHERE users.sign_in_count > 10
)

We see that the subquery isn't correlated, which is fine when the inner and outer tables differ. When they are the same, we need to correlate the inner and outer tables by id to effectively limit the outer table. Obviously this is a bad use case since the correlated subquery is less performant than using a simple WHERE clause, but there are plenty of other use cases where you can greatly increase query performance.

Additionally, the 'SELECT *' clause for the subquery is unnecessary and should be replaced by 'SELECT 1'. The difference is small and difficult to see given that they both result in the exact same query plan (since the semantics of EXISTS dictate that it doesn't need any of those columns anyway). In the planning stages, however, using 'SELECT *' will force us to look up the table meta-data to find the list of columns to bind, which are then trashed later.