Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

[BP to 7.1.1] - WITH clause distribution over union queries deviates from sql standard

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

Components

Affects versions

Fix versions

Labels

Environment

None

Link to Log File, atop/blg, CBCollectInfo, Core dump

None

Release Notes Description

None

Activity

Show:

CB robot October 7, 2022 at 8:56 AM

Build couchbase-server-7.2.0-5000 contains query commit 175b287 with commit message:
Fix scope of CTE to cover setop queries

Bingjie Miao June 13, 2022 at 8:57 PM
Edited

Yes this is expected, we allow a "full select" under set operations (UNION, etc), this is above and beyond what SQL standard
allows, to be compatible with Postgres, as well as previous Couchbase server versions. A "full select" means a SELECT statement with optional WITH clause in front of it, as well as optional ORDER BY clause. Note that if a "full select" is used under set options (e.g. UNION) the "full select" must be enclosed in parentheses.

Yes an error is expected, since here we don't have any set operations, only a single SELECT, in which case we don't allow
nested WITH clause specification. Similar error is returned from Postgres and Oracle.

This is the same as #4 above.

Pierre Regazzoni June 13, 2022 at 6:37 PM

Ran on 7.1.1-3141 and see some diff. can you confirm behavior

CB robot June 1, 2022 at 9:30 PM

Build couchbase-server-7.1.1-3110 contains query commit 175b287 with commit message:
Fix scope of CTE to cover setop queries

Fixed
Pinned fields
Click on the next to a field label to start pinning.

Details

Assignee

Reporter

Is this a Regression?

Unknown

Triage

Untriaged

Priority

Instabug

Open Instabug

PagerDuty

Sentry

Zendesk Support

Created June 1, 2022 at 4:26 PM
Updated October 7, 2022 at 8:56 AM
Resolved June 13, 2022 at 8:59 PM
Configure
Instabug
Loading...