Not avaiable alias is used inside CTE without an error
------------------------------------------------------
Key: CORE-6267
URL: http://tracker.firebirdsql.org/browse/CORE-6267
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.5, 3.0.6
Reporter: Karol Bieniaszewski
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
)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
