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

Add support for nested CTEs [CORE6100] #6349

Open
firebird-automations opened this issue Jul 15, 2019 · 7 comments
Open

Add support for nested CTEs [CORE6100] #6349

firebird-automations opened this issue Jul 15, 2019 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: 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
)

=>

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

In this case it means - with clause can't be used in sub-select's (nested queries).
BTW, there is no reason to do it, move CTE Y into first WITH clause.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

consider application logic you have many forms and in every form you have select.
every select can have own with clause.

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
2. Form2 have query which is main for it
3. Form3 have query which is main for it

but when you do query in Form2 you can use query from 1 and 2 as subqueries
You do not move every with clause to the top query as this is not usefull and sometimes not possible if you have same name for CTE but it have difference in content.

-----------------------------------------------
But why this is not possible to use now?
I can have top query with WITH clause.
I can have multiple subselects with own WITH clause.

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

AND EXISTS(
WITH Z AS
(
SELECT 6 AS NUMBER FROM RDB$DATABASE
)
SELECT * FROM Z INNER JOIN RDB$RELATION_FIELDS RF ON 1=1 WHERE RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME AND RF.RDB$FIELD_POSITION>Z.NUMBER
)

AND EXISTS(
WITH Q AS
(
SELECT 6 AS NUMBER FROM RDB$DATABASE
)
SELECT * FROM Q INNER JOIN RDB$RELATION_FIELDS RF ON 1=1 WHERE RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME AND RF.RDB$FIELD_POSITION>Q.NUMBER
)

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Oracle supports WITH in derived tables/subselects.
It should be supported in Firebird too. SQL generators know nothing the underlying SELECT just wraps them, so now SQLs with CTEs cannot be wrapped in Firebird.

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

@sean, A WITH clause in a subquery is explicitly allowed in the SQL standard syntax. For example, derivation for EXISTS:

```
<exists predicate> ::=
EXISTS <table subquery>

::=

<subquery> ::=
<left paren> <query expression> <right paren>

<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
```

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.

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