Why this works in the most of DBMSs, but in Ultralite doesn't?
Column 'T1.idTable1' not found
Ps: This example is just a very simple scenario. It could be easily solved by replacing the sub-select to joins. But the question is still valid for other complex scenarios we have today!
Just to complement, this limitation only occurs in joins. If you move the filter condition to the where clause, this works, as follow:
The problem here is that the ON clause isn't allowed to refer to tables other than those in the associated join. Please see this note in the documentation.
Also note that conditions in the WHERE clause can have unexpected effect with outer joins. For inner joins, the ON and WHERE clause are semantically equivalent, but this is not true for outer joins.
answered 14 Feb, 17:24
THIS IS NOT an official answer. ...but...
In your case, neither of your 2 uses of sub-queries have official support so the fact that it works in the latter case is a unofficial bonus. If I had to guess, the UltraLite's parser is recognizing your latter query as a correlated sub-query and may be flattening/rewriting that (getting the plan for that might tell more).
Given the noted 'official' restriction, your first query probably does not look like a proper correlated subquery and so the parser unit will likely just be looking at correlation names (tokens) from the subquery alone.
answered 01 Feb, 11:56
Nick Elson S...