Issue Details (XML | Word | Printable)

Key: CORE-6100
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Karol Bieniaszewski
Votes: 1
Watchers: 7
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Add support for nested CTEs

Created: 15/Jul/19 10:06 AM   Updated: 20/Jul/19 09:08 AM
Component/s: Engine
Affects Version/s: 3.0.4, 3.0.5
Fix Version/s: None

QA Status: No test


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 15/Jul/19 10:41 AM
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.

Karol Bieniaszewski added a comment - 15/Jul/19 10:53 AM - edited
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
)


Sean Leyne added a comment - 15/Jul/19 04:29 PM
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.

Attila Molnár added a comment - 17/Jul/19 06:19 AM
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.

Mark Rotteveel added a comment - 20/Jul/19 09:06 AM - edited
@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>

<table subquery> ::=
  <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.