Why Ultralite doesn't support sub-query linking in join conditions?

[Former Member]

Quick question:

Why this works in the most of DBMSs, but in Ultralite doesn't?

SELECT 
    (
        SELECT cdTable2 
        FROM table2 T2
        INNER JOIN T1xT2
            ON T1xT2.idTable2 = T2.idTable2
            AND T1xT2.idTable1 = T1.idTable1
    )
FROM table1 T1

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:

SELECT 
    (
        SELECT cdTable2 
        FROM table2 T2
        INNER JOIN T1xT2
            ON T1xT2.idTable2 = T2.idTable2
        WHERE T1xT2.idTable1 = T1.idTable1
    )
FROM table1 T1

[VolkerBarth]

Don't know, but what's a "SGBD"?

[Former Member]

Sorry, wrong acronym (pt-br instead). The right is DBMS.

[former_SQLA_member1694876]

This may be a bug - thank you for posting it.

Hopefully using the where clause is a suitable workaround?

[Former Member]

Yes, it is! But some joins, like a simple LEFT turns the things a little bit confusing (WHERE T IS NULL OR T.id = XXX). I wonder if this can cause some trouble in the query plan... Thank you for replying this, BTW! 😃

[former_SQLA_member1694876]

Indeed, the ON and WHERE clause are semantically equivalent only for inner joins! (See the documentation note in my answer below.)

[former_SQLA_member1694876 · Accepted solution]

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.

[Former Member]

THIS IS NOT an official answer. ...but...

The UL parser (being 'light' like the rest of Ultra*Lite*) supports a more limited syntax (ie. a subset of SQL) and only some semantics that may be available in other RDBMS.

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.