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
Add support for nested CTEs [CORE6100] #6349
Comments
Modified by: @livius2description: Firebird incorrectly check for nested CTE. WITH X AS SELECT * FROM RDB$RELATIONS R NOT EXISTS( => Firebird incorrectly check for nested CTE. WITH X AS SELECT * FROM RDB$RELATIONS R NOT EXISTS( ------------------------------ |
Commented by: @hvlad In this case it means - with clause can't be used in sub-select's (nested queries). |
Commented by: @livius2 consider application logic you have many forms and in every form you have select. you use this form query result as some where clause in your other select e.g. you have 3 forms 1. Form1 have query which is main for it but when you do query in Form2 you can use query from 1 and 2 as subqueries ----------------------------------------------- But i can not have both? I see no reason for that. e.g. this is working - multiple subselects with own CTE but for main query i cannot specify it. SELECT * FROM RDB$RELATIONS R NOT EXISTS( AND EXISTS( AND EXISTS( |
Commented by: Sean Leyne (seanleyne) Karol, I have done some searching and I can't find _any_ examples where sub-queries contain WITHs, when the main query has a WITH. So, it seems that what you are trying to do is not valid. |
Modified by: Sean Leyne (seanleyne)priority: Major [ 3 ] => Minor [ 4 ] issuetype: Bug [ 1 ] => Improvement [ 4 ] summary: Incorrect check for nested CTE => Add support for nested CTEs |
Commented by: Attila Molnár (e_pluribus_unum) Oracle supports WITH in derived tables/subselects. |
Commented by: @mrotteveel @sean, A WITH clause in a subquery is explicitly allowed in the SQL standard syntax. For example, derivation for EXISTS: ``` <subquery> ::= <query expression> ::= And disallowing nested WITH clauses (although the example from this ticket isn't even nesting) seems to be an artificial restriction that doesn't derive from the SQL standard syntax. |
Submitted by: @livius2
Votes: 3
Firebird incorrectly check for nested CTE.
Below query have not nested CTE
WITH X AS
(
SELECT 6 AS NUMBER FROM RDB$DATABASE
)
SELECT * FROM RDB$RELATIONS R
WHERE
NOT EXISTS(
WITH Y AS
(
SELECT 6 AS NUMBER FROM RDB$DATABASE
)
SELECT * FROM Y INNER JOIN RDB$RELATION_FIELDS RF ON 1=1 WHERE RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME AND RF.RDB$FIELD_POSITION>Y.NUMBER
)
------------------------------
Dynamic SQL Error
SQL error code = -104
WITH clause can't be nested.
The text was updated successfully, but these errors were encountered: