While perfroming inner join in one of the nql query we are getting error in CB version 6.0 and same query working in version 6.6. After further reading we found that in version 6.5, N1QL adds support for expression and subquery as right-hand side of an ANSI JOIN. Previously in version 5.5 and 6.0, the right-hand side of an ANSI JOIN must be a keyspace. Can you please share such simple example which demonstrates this difference with both version 6.6 and 6.0 so that we will explain same to developer and they will rewrite the code accordingly.
“errors”:[{“code”:3000,“message”:“ANSI JOIN must be done on a keyspace. - at inner \n Error while parsing: runtime error: invalid memory address or nil pointer dereference - at inner”,“retry”:false}]
I should probably augment my response with a note that Couchbase 6.0 reached end-of-life status in October 2022, so you probably want to ensure the 6.0 cluster(s) are upgraded in preference to any other approach.
Thanks @dh and @vsr1 for your prompt response so in short in 6.0 we should only mention keyspace (bucket) in rhs side no expressions are allowed. One more thing is there any difference in keyspace or bucket or both are same.
@vsr1 and @dh, actually we had one screen loading query which contains expression in RHS but the CB version is 6.0 and customer is not ready to upgrade immediately. Is there any way we can rewrite the query. Can you please share any blog or article which we can follow to achieve the same.
Thanks,
Debasis
As you don’t have ANY OUTER JOIN.
One thing you can try is , when you have right side expression convert that to UNNEST and move corresponding ON clause to WHERE clause. Things may slow, but it should work.
Suggestion is straight forward. One already followed in your query.
SELECT FROM b1 AS b1
INNER JOIN
(SELECT .....) AS b2 ON b1.c1 = b2.c1
WHERE b1.c2 = 10;
TO
SELECT FROM b1 AS b1
UNNEST
(SELECT .....) AS b2
WHERE b1.c2 = 10 AND b1.c1 = b2.c1;
Change INNER JOIN to UNNEST
Move ON clause to WHERE clause
Whole query is:
SELECT t178_0.`id` f_0,
CASE WHEN t178_0.`host_type` = 1 THEN 'Network' ELSE ' ' END f_1,
CASE WHEN t178_0.`host_value` = 5808 THEN 'Verizon Wireless(311,480)' ELSE 'T-Mobile USA Inc(310,260)' END f_2,
CASE WHEN t178_0.`granularity_type` = 0 THEN 'Global' ELSE CASE WHEN t178_0.`granularity_type` = 1 THEN 'Network' ELSE CASE WHEN t178_0.`granularity_type` = 2 THEN 'Country' ELSE 'Alliance' END END END f_3,
t178_0.`partner_value` f_4, t178_0.`host_type` f_5, t178_0.`host_value` f_6, t178_0.`granularity_type` f_7,
t178_0.`granularity_value` f_8, t178_0.`reportid` f_9, t178_0.`reportname` f_10, t178_0.`kpi_id` f_11,
t178_0.`kpi_name` f_12, t178_0.`kpi_category` f_13, t178_0.`cat_desc` f_14, t178_0.`measurement_type` f_15,
t178_0.`measurementdescr` f_16, t178_0.`measurement_intvl` f_17, t178_0.`intervaldescr` f_18,
t178_0.`error_causing_nwtypes` f_19, CASE WHEN t178_0.`error_causing_nwtypes` = 0 THEN 'Not Applicable' ELSE CASE WHEN t178_0.`error_causing_nwtypes` = 1 THEN 'Host Network' ELSE 'Partner Network' END END f_20,
t178_0.`error_code` f_21,
CASE WHEN t178_0.`error_code` IN ['-1', '-100'] THEN 'Not Applicable' ELSE t178_0.`error_code_desc` END f_22,
t178_0.`apn` f_23,
CASE WHEN t178_0.`apn` = '-1' THEN 'Not Applicable' ELSE t178_0.`apn` END f_24,
t178_0.`scm_cos_id` f_25, t178_0.`scm_cos_name` f_26
FROM ( SELECT a.id, a.reportid, b.reportname, a.kpi_id, c.kpi_name, a.kpi_category, a.measurement_type, f.measurementdescr,
a.measurement_intvl, e.intervaldescr, a.granularity_type, a.granularity_value, g.granularity_value AS partner_id,
g.partner_value, a.host_type, a.host_value, a.categorydescription, d.categorytype AS cat_type,
d.categorydescription AS cat_desc, a.assigned_user AS notify_user, a.lower_threshold, a.lower_alarm_type,
a.upper_threshold, a.upper_alarm_type, a.error_causing_nwtypes, a.cause_code AS error_code, h.errorname AS error_code_desc,
a.cos_id AS scm_cos_id, i.cname AS scm_cos_name, a.interconnect_carrier, a.partnernetworkpreference, a.steeringcos, a.apn
FROM config a
INNER JOIN config b ON a.reportid = b.reportid
INNER JOIN config c ON a.reportid = c.reportid AND a.kpi_id = c.kpi_id
INNER JOIN config d ON a.kpi_category = d.categorytype
INNER JOIN config e ON a.measurement_intvl = e.intervalid
INNER JOIN config f ON a.measurement_type = f.measurementtype
UNNEST ( SELECT ad.granularity_value AS granularity_value,
REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_DISTINCT(ARRAY_AGG(gh.partner_value))),"\",\"",","),"[\"",""),"\"]", "") AS partner_value,
gh.partner_type AS partner_type
FROM config ad
UNNEST ( SELECT 0 AS partner_type, -1 AS partner_id, 'Not Applicable' AS partner_value
UNION
SELECT 1 AS partner_type, networkid AS partner_id, networkname AS partner_value
FROM config WHERE __t = 'irdb-net'
UNION
SELECT 2 AS partner_type, countryid AS partner_id, countryname AS partner_value
FROM config WHERE __t = 'irdb-country'
UNION
SELECT 3 AS partner_type, grpid AS partner_id, name AS partner_value
FROM config WHERE __t = 'irdb-ng') gh
WHERE ad.__t = 'rcem-kpi-threshold-config' AND ad.granularity_type = gh.partner_type
AND ANY v IN SPLIT(ad.granularity_value,"|") SATISFIES v=TO_STRING(gh.partner_id) END
GROUP BY ad.granularity_value, gh.partner_type ) g
UNNEST ( SELECT DISTINCT x.cid AS cid , x.cname AS cname
FROM config AS config
UNNEST config.cos AS x
WHERE config.__t = 'sds-cos-rules' AND config.feature = 'RCEMVIP') i
UNNEST ( SELECT DISTINCT b1.reportid AS report_id,
b1.cause_code AS causecode,
REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_DISTINCT(ARRAY_AGG(a1.error_name))),"\",\"",","),"[\"",""),"\"]", "") AS errorname
FROM config AS b1
UNNEST ( SELECT "7" AS report_id, errorcode, error_name
FROM config WHERE __t= 'rcem-map-errorcodes'
UNION
SELECT "17" AS report_id, ri_error_code AS errorcode, error_name
FROM config WHERE __t='rcem-dia-errorcodes'
UNION
SELECT "12" AS report_id, causecode AS errorcode, causedescription AS error_name
FROM config WHERE __t='rcem-gtp-causecodes'
UNION
SELECT "56,57,18,3,12,17,7" AS report_id, '-100' AS errorcode, '' AS error_name ) a1
WHERE b1.__t = 'rcem-kpi-threshold-config'
AND ANY v IN SPLIT(b1.cause_code,",") SATISFIES v=TO_STRING(a1.errorcode) END
AND ANY v IN SPLIT(a1.report_id,",") SATISFIES v=TO_STRING(b1.reportid) END
GROUP BY b1.cause_code, b1.reportid ) h
WHERE a.__t = 'rcem-kpi-threshold-config'
AND b.__t = 'rcem-kpi-reportid'
AND c.__t = 'rcem-kpi-master'
AND d.__t = 'rcem-kpi-categories'
AND e.__t = 'rcem-kpi-measurement-intvls'
AND f.__t = 'rcem-kpi-measurement-types'
AND h.causecode=a.cause_code
AND a.granularity_type = g.partner_type AND a.granularity_value = g.granularity_value
AND a.cos_id = i.cid
AND a.reportid = h.report_id) t178_0
WHERE (t178_0.`reportid` = 7)
AND (t178_0.`granularity_type` = 0);