Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.5.0, 6.6.5, 7.0.3, 7.1.0
-
Untriaged
-
Unknown
Description
SQL and N1QL currently behave differently when dealing with with clauses before a series of unions. It seems to me that SQL accepts only 1 with clause per level, union queries are considered on the same level, and if these union queries contain with clauses the clause will overwrite previous with clauses. In N1QL, the with clause only attaches to the first select statement after the with clause and this only happens if the select is not in a subquery, i.e same level. Here are some examples to make this more clear:
n1ql:
1. WITH cte AS (SELECT 1 as a) SELECT cte.a from cte union all SELECT cte.a from cte --> error
2. WITH cte AS (SELECT 1 as a) (SELECT cte.a from cte) union all (SELECT cte.a from cte) --> error
3. WITH cte AS (SELECT 1 as a) (SELECT cte.a from cte) union all SELECT cte.a from cte --> error
4. WITH cte1 AS (SELECT 1 as a) (with cte2 as (SELECT 2 as b) SELECT cte1.a from cte1) union all SELECT cte1.a from cte1 --> error
5. WITH cte1 AS (SELECT 1 as a) (with cte2 as (SELECT 2 as b) SELECT cte1.a from cte1) --> error
6. WITH cte1 AS (SELECT 1 as a) (with cte2 as (SELECT 2 as b) SELECT cte1.a from cte1) union all (SELECT cte1.a from cte1) --> error
7. WITH cte AS (SELECT 1 as a) SELECT cte.a from cte union all WITH cte AS (SELECT 1 as a) SELECT cte.a from cte --> works
8. (WITH cte AS (SELECT 1 as a) SELECT cte.a from cte) union all (WITH cte AS (SELECT 1 as a) SELECT cte.a from cte); --> works
9. WITH cte AS (SELECT 1 as a) (SELECT cte.a from cte) union all (SELECT cte.a from cte) --> error
10. WITH cte AS (SELECT 1 as a) (with cte2 as (select 2 as a) SELECT cte.a from cte) union all (SELECT cte.a from cte) --> error
11. WITH cte1 AS (SELECT 1 as a) SELECT cte1.a from cte1 union all with cte2 as (SELECT 2 as a) SELECT cte2.a from cte2 --> works
12. WITH cte1 AS (SELECT 1 as a) SELECT cte1.a from cte1 union all (with cte2 as (SELECT 2 as a) SELECT cte2.a from cte2) --> works
sql:
1. WITH cte AS (SELECT 1 as a) SELECT cte.a from cte union all SELECT cte.a from cte; --> return two 1s
2. WITH cte AS (SELECT 1 as a) (SELECT cte.a from cte) union all (SELECT cte.a from cte); --> returns two 1s
3. WITH cte AS (SELECT 1 as a) (SELECT cte.a from cte) union all SELECT cte.a from cte; --> returns two 1s
4. WITH cte1 AS (SELECT 1 as a) (with cte2 as (SELECT 2 as b) SELECT cte1.a from cte1) union all SELECT cte1.a from cte1; --> error
5. WITH cte1 AS (SELECT 1 as a) (with cte2 as (SELECT 2 as b) SELECT cte1.a from cte1); --> return one 1
6. WITH cte1 AS (SELECT 1 as a) (with cte2 as (SELECT 2 as b) SELECT cte1.a from cte1) union all (SELECT cte1.a from cte1); --> error
7. WITH cte AS (SELECT 1 as a) SELECT cte.a from cte union all WITH cte AS (SELECT 1 as a) SELECT cte.a from cte --> error
8. (WITH cte AS (SELECT 1 as a) SELECT cte.a from cte) union all (WITH cte AS (SELECT 1 as a) SELECT cte.a from cte); --> works
9. WITH cte AS (SELECT 1 as a) (SELECT cte.a from cte) union all (SELECT cte.a from cte); --> works
10. WITH cte AS (SELECT 1 as a) (with cte2 as (select 2 as a) SELECT cte.a from cte) union all (SELECT cte.a from cte); --> error
11. WITH cte1 AS (SELECT 1 as a) SELECT cte1.a from cte1 union all with cte2 as (SELECT 2 as a) SELECT cte2.a from cte2 --> error
12. WITH cte1 AS (SELECT 1 as a) SELECT cte1.a from cte1 union all (with cte2 as (SELECT 2 as a) SELECT cte2.a from cte2) --> works
Attachments
Issue Links
For Gerrit Dashboard: MB-52412 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
175626,2 | MB-52412 Fix scope of CTE to cover setop queries | neo | query | Status: MERGED | +2 | +1 |