Issue Details (XML | Word | Printable)

Key: CORE-6267
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

CTE with reference to outside alias/column raises error

Created: 23/Mar/20 09:40 AM   Updated: 23/Mar/20 03:34 PM
Component/s: Engine
Affects Version/s: 3.0.5, 3.0.6
Fix Version/s: None

QA Status: No test


 Description  « Hide
Alias "U" is not available in the CTE "ZAWIESZENIA".
If you remove whole WHERE from the main query then an error is raised.
--------------------------
SQL error code = -206
Column unknown
U.UMOWA_DATA_DO
At line 8, column 190.
---------------------------

but if you add whole where no error present.
It can be releated somehow with window functions.

-----------------------------------------------------------------------------------------------------------------------------

WITH DATY_ZAWIESZEN AS
    (
        SELECT date '2020-01-01' AS DZ_OD, date '2020-01-31' AS DZ_DO FROM RDB$DATABASE
    )
    , ZAWIESZENIA AS
    (
        SELECT W_UZ.UMOWA_ID,
               U.UMOWA_DATA_DO /* ------------ ALIAS "U" IS NOT AVAILABLE HERE!!!! ------------ */ AS ZAWIESZ_DATA_DO
          FROM (SELECT 1 AS UMOWA_ID FROM RDB$DATABASE ) W_UZ
               INNER JOIN DATY_ZAWIESZEN W_DZ ON 1=1
    )
SELECT U.UMOWA_ID
  FROM (SELECT CURRENT_DATE AS UMOWA_DATA_DO, 2 AS UMOWA_ID FROM RDB$DATABASE) U
        INNER JOIN DATY_ZAWIESZEN DZ ON 1=1

/* ------------ without this WHERE an error is raised about unknown column UMOWA_DATA_DO ------------ */
    WHERE
        EXISTS
        (
           SELECT *
             FROM ZAWIESZENIA UZ
            WHERE UZ.UMOWA_ID = U.UMOWA_ID
        )

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 23/Mar/20 11:41 AM - edited
Without a sub-select in WHERE clause, the main query doesn't reference CTE ZAWIESZENIA.
Engine checks even unused CTE's as it could contain parameter markers. In this case
CTE ZAWIESZENIA itself have no relation with alias U and error is raised. This is correct.

When CTE ZAWIESZENIA is used at WHERE clause it is expanded as derived table and now
it have access to the alias U. I can't say right now if it is correct or not, but this is how thing
works currently.

PS as you see, it have no relation with window functions

Karol Bieniaszewski added a comment - 23/Mar/20 11:53 AM
I have replaced query with your simplification to make issue text clearer

Sean Leyne added a comment - 23/Mar/20 03:34 PM
I always understood that 'external' references needed to be passed to CTE via parameters, that the CTE could not reference external alias/columns.