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

asked 01 Feb '17, 07:58

Alex's gravatar image

Alex
1.1k274756
accept rate: 25%

edited 01 Feb '17, 10:26

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

(01 Feb '17, 09:48) Volker Barth
Replies hidden

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

(01 Feb '17, 10:26) Alex
2

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

Hopefully using the where clause is a suitable workaround?

(03 Feb '17, 12:50) Tim McClements
Replies hidden
1

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! =)

(03 Feb '17, 14:37) Alex
1

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

(14 Feb '17, 17:31) Tim McClements

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.

permanent link

answered 14 Feb '17, 17:24

Tim%20McClements's gravatar image

Tim McClements
2.0k1830
accept rate: 35%

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.

permanent link

answered 01 Feb '17, 11:56

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×161
×94
×25
×17

question asked: 01 Feb '17, 07:58

question was seen: 1,882 times

last updated: 14 Feb '17, 17:31