Skip to content
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

Open
firebird-automations opened this issue Mar 23, 2020 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

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
)

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I have replaced query with your simplification to make issue text clearer

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

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".
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.RDB$RELATION_ID, W_UZ.RDB$CHARACTER_SET_NAME, W_UZ.UMOWA_ID, MAXVALUE(W_UZ.ZAWIESZ_DATA_OD, W_U.UMOWA_DATA_OD) AS ZAWIESZ_DATA_OD, MINVALUE(COALESCE(W_UZ.ZAWIESZ_DATA_DO, W_DZ.DZ_DO), U.UMOWA_DATA_DO /* ------------ ALIAS "U" IS NOT AVAILABLE HERE!!!! ------------ */) AS ZAWIESZ_DATA_DO
FROM
(SELECT CURRENT_DATE AS ZAWIESZ_DATA_OD, CURRENT_DATE AS ZAWIESZ_DATA_DO, RDB$RELATION_ID, RDB$CHARACTER_SET_NAME, 1 AS UMOWA_ID FROM RDB$DATABASE ) W_UZ
INNER JOIN (SELECT CURRENT_DATE AS UMOWA_DATA_OD FROM RDB$DATABASE ) W_U ON 1=1
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
(
SELECT
ISLANDS.UMOWA_ID
, ISLANDS.ISLAND_NR
, MIN(ISLANDS.START_DATE) AS ISLAND_START_DATE
, MAX(ISLANDS.END_DATE) AS ISLAND_END_DATE
FROM
(SELECT
GROUPS.*
, CASE WHEN GROUPS.PREV_END_DATE >= START_DATE THEN 0 ELSE 1 END AS IS_ISLAND_START
, SUM(CASE WHEN GROUPS.PREV_END_DATE >= START_DATE THEN 0 ELSE 1 END) OVER (PARTITION BY GROUPS.UMOWA_ID ORDER BY GROUPS.RN) AS ISLAND_NR
FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY UZ.UMOWA_ID ORDER BY UZ.ZAWIESZ_DATA_OD, COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO)) AS RN
, UZ.UMOWA_ID
, UZ.ZAWIESZ_DATA_OD AS START_DATE
, COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO) AS END_DATE
, LAG(COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO), 1) OVER(PARTITION BY UZ.UMOWA_ID ORDER BY UZ.ZAWIESZ_DATA_OD, COALESCE(UZ.ZAWIESZ_DATA_DO, DZ.DZ_DO)) AS PREV_END_DATE
FROM ZAWIESZENIA UZ
WHERE
UZ.UMOWA_ID=U.UMOWA_ID
) GROUPS
) ISLANDS
GROUP BY
ISLANDS.UMOWA_ID
, ISLANDS.ISLAND_NR
ORDER BY
ISLAND_START_DATE
) X
WHERE
DZ.DZ_OD>=X.ISLAND_START_DATE AND DZ.DZ_DO<=X.ISLAND_END_DATE
)

=>

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".
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
)

@firebird-automations
Copy link
Collaborator Author

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".
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
)

=>

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
)

summary: Not avaiable alias is used inside CTE without an error => CTE with reference to outside alias/column raises error

@firebird-automations
Copy link
Collaborator Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant