New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
CTE with reference to outside alias/column raises error [CORE6267] #6509
Comments
Commented by: @hvlad Without a sub-select in WHERE clause, the main query doesn't reference CTE ZAWIESZENIA. When CTE ZAWIESZENIA is used at WHERE clause it is expanded as derived table and now PS as you see, it have no relation with window functions |
Commented by: @livius2 I have replaced query with your simplification to make issue text clearer |
Modified by: @livius2description: Look at this not trivial query but migrated to use only system tables to show the problem. Alias "U" is not available in the CTE "ZAWIESZENIA". but if you add whole where no error present. ----------------------------------------------------------------------------------------------------------------------------- WITH DATY_ZAWIESZEN AS SELECT => Look at this not trivial query but migrated to use only system tables to show the problem. Alias "U" is not available in the CTE "ZAWIESZENIA". but if you add whole where no error present. ----------------------------------------------------------------------------------------------------------------------------- WITH DATY_ZAWIESZEN AS
SELECT U.UMOWA_ID /* ------------ without this WHERE an error is raised about unknown column UMOWA_DATA_DO ------------ */ |
Modified by: Sean Leyne (seanleyne)description: Look at this not trivial query but migrated to use only system tables to show the problem. Alias "U" is not available in the CTE "ZAWIESZENIA". but if you add whole where no error present. ----------------------------------------------------------------------------------------------------------------------------- WITH DATY_ZAWIESZEN AS
SELECT U.UMOWA_ID /* ------------ without this WHERE an error is raised about unknown column UMOWA_DATA_DO ------------ */ => Alias "U" is not available in the CTE "ZAWIESZENIA". but if you add whole where no error present. ----------------------------------------------------------------------------------------------------------------------------- WITH DATY_ZAWIESZEN AS /* ------------ without this WHERE an error is raised about unknown column UMOWA_DATA_DO ------------ */ summary: Not avaiable alias is used inside CTE without an error => CTE with reference to outside alias/column raises error |
Commented by: Sean Leyne (seanleyne) I always understood that 'external' references needed to be passed to CTE via parameters, that the CTE could not reference external alias/columns. |
Submitted by: @livius2
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
)
The text was updated successfully, but these errors were encountered: