INTERSECT Query Issue - CB workbench

N1QL
1

Hi team,
we have a query working in CB-6.6X version and the is not working in CB 7.1.X version. Is something changed?

Query:

WITH 1_table AS (SELECT RAW route.RteId FROM `default` AS route WHERE route.`$Type` = 'XXXX')
SELECT DISTINCT ProdUpcVal FROM `default` WHERE `$Type` = 'XXXXX' 
AND RteCtryCd = 'XXX' AND RteId IN 1_table
INTERSECT
WITH 2_table AS (SELECT RAW route.RteId FROM `default` AS route WHERE route.`$Type` = 'XXXX')
SELECT DISTINCT ProdUpcVal FROM `default` WHERE `$Type` = 'XXXXX' 
AND RteCtryCd = 'XXX' AND RteId IN 2_table;

if i change the query like below then it is working.

(WITH 1_table AS (SELECT RAW route.RteId FROM `default` AS route WHERE route.`$Type` = 'XXXX')
SELECT DISTINCT ProdUpcVal FROM `default` WHERE `$Type` = 'XXXXX' 
AND RteCtryCd = 'XXX' AND RteId IN 1_table)
INTERSECT
(WITH 2_table AS (SELECT RAW route.RteId FROM `default` AS route WHERE route.`$Type` = 'XXXX')
SELECT DISTINCT ProdUpcVal FROM `default` WHERE `$Type` = 'XXXXX' 
AND RteCtryCd = 'XXX' AND RteId IN 2_table);
2

Yes, a deviation from SQL standard has been removed - the change came about in https://issues.couchbase.com/browse/MB-52412 (first in 7.1.1).

Your change is what is now expected.

HTH.

1 Like
3

If Both WITH clauses same. CTE able to use in all set OP queries. That is MB-52412 addressed

WITH 1_table AS (SELECT RAW route.RteId 
                               FROM `default` AS route 
                               WHERE route.`$Type` = 'XXXX')
SELECT DISTINCT ProdUpcVal FROM `default` WHERE `$Type` = 'XXXXX' 
AND RteCtryCd = 'XXX' AND RteId IN 1_table
INTERSECT
SELECT DISTINCT ProdUpcVal FROM `default` WHERE `$Type` = 'XXXXX' 
AND RteCtryCd = 'XXX' AND RteId IN 1_table;
4

Thanks @vsr1 , we have different condition for the WITH condition. i haven’t added those condition in this query. For ex: For the first WITH condition i will be having routeNumber as “1” and second one will have the routeNumber as “2”