ANSI Join error with joining sub queries

SQL++
1

Hi ,

We are facing below error when we run N1QL with sub query. Same query is working in 6.5.1, but not working in 6.0.4.

N1QL:
SELECT DISTINCT t191_0.dgid f_0,
t191_0.dgname f_1,
t191_0.hnwid f_2,
t189_0.devicename f_3,
t189_0.manufacturername f_4,
t189_0.technologytype f_5
FROM config t191_0 LEFT
OUTER JOIN (
SELECT __t,
devicename,
dgid,
hnwid,
manufacturername,
technologytype
FROM config
WHERE __t=“ntr-devicegroupm-filter” ) t189_0 ON t191_0.dgid = t189_0.dgid
WHERE (t191_0.__t= ‘ntr-devicegroupm’)
ORDER BY t191_0.dgname ASC

Error:
" ANSI JOIN must be done on a keyspace. - at INNER Error while parsing: runtime error: invalid memory address or nil pointer dereference - at INNER " this one

Thanks,
Vikas

2

Right side of JOIN as expression is supported only in 6.5+ https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/

Alternative way representation,

SELECT DISTINCT l.dgid f_0,
       l.dgname f_1,
       l.hnwid f_2,
       (ARRAY v FOR v IN df WHEN v.dgid = l.dgid END) AS filters
FROM config  AS l
LET df =  (SELECT devicename, dgid, manufacturername, technologytype
           FROM config AS d
           WHERE __t = "ntr-devicegroupm-filter" )
WHERE l.__t= "ntr-devicegroupm"
ORDER BY l.dgname ASC
3

Hi,
getting below error to execute this query.

3000 syntax error - at AS SELECT DISTINCT l.dgid f_0, l.dgname f_1, l.hnwid f_2, (ARRAY v FOR v IN df WHEN v.dgid = l.dgid END) AS filters FROM config t191_0 AS l LET df = (SELECT devicename, dgid, manufacturername, technologytype FROM config AS d WHERE __t = “ntr-devicegroupm-filter” ) WHERE l.__t= “ntr-devicegroupm” ORDER BY l.dgname ASC

4 
SELECT DISTINCT l.dgid f_0,
       l.dgname f_1,
       l.hnwid f_2,
       (ARRAY v FOR v IN df WHEN v.dgid = l.dgid END) AS filters
FROM config  AS l
LET df =  (SELECT devicename, dgid, manufacturername, technologytype
           FROM config AS d
           WHERE __t = "ntr-devicegroupm-filter" )
WHERE l.__t= "ntr-devicegroupm"
ORDER BY l.dgname ASC