ANSI JOIN must be done on a keyspace Error in CB Version6.0

Hi Team,

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}]

Thanks,
Debasis

You have a nested panic there - there likely will be a stack in the query.log relating to it; I expect it would identify a fixed defect.

A 6.0 supported ANSI join would be in the form:

SELECT ... 
FROM keyspace1 JOIN keyspace2 ON keyspace1.field1 = keyspace2.field1

Ref: https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/join.html

The 6.0 documentation is clear what is permitted for the right-hand side:
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/join.html#ansi-join-rhs
You can contrast that to current right-hand side support:
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/join.html#ansi-join-rhs
where you’ll see the extension of the supported syntax.

HTH.

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.

Ref: https://www.couchbase.com/support-policy/enterprise-software/ (See the section: “5. End of Life Schedule”)

1 Like

If you are not using Outer JOIN, leftside is keyspace switch the JOIN order and if need use HASH JOIN hint.

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.

Thanks,
Debasis

keyspace is general term (in 7.0+ it can be collection or bucket both consider). You can consider both same .

1 Like

@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

If not the Outer JOIN switch the JOIN. Otherwise no option. If need help post the query will see any alternatives

Is there anyway we can attach the query in text format.

Thanks,
Debasis

You can attach a ZIP file of the query using the upload button in the response editor, if this helps?

KPI_Threshold_Query.zip (1.6 KB)
Please find the attached query.
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.

cc @bingjie.miao

Thanks @vsr1 . Can you show some examples so that I can ask DEV team to do it.

Thanks,
Debasis

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);
1 Like