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
There are no subversion log entries for this issue yet.