[sqlite] Correlated subquery throwing an error

[Clemens Ladisch]

Gary Briggs wrote:
>> SELECT
>>   a.id AS a_id,
>>   (SELECT b.id
>>    FROM foo AS b
>>    WHERE b.id!=a.id
>>      AND distance(a.x,a.y,b.x,b.y)<=25
>>    ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
>> FROM foo AS a
>
> This is the bit that doesn't seem to work; having
> distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
> what appears to cause the error that it can't find a.x, from the outer query.

I don't know why correlated subqueries cannot use values from the outer
query in the ORDER BY or LIMIT clauses; this does not look as if it were
by design.

Anyway, I got it to work with another indirection:

SELECT foo.*,
       (SELECT id
        FROM (SELECT id,
                     x,
                     y,
                     foo.x AS foo_x,
                     foo.y AS foo_y,
                     val
              FROM foo)
        WHERE DIST(foo_x, foo_y, x, y) < 25
        ORDER BY val, DIST(foo_x, foo_y, x, y)
        LIMIT 1
       ) AS id2
FROM foo


Regards,
Clemens