SQL++ error since 7.6.2 update

SQL++
1

Since updating from 7.2.2 to 7.6.2 we are receiving error messages on our queries, that I was not able to solve yet. We did not change the queries and have the system running with multiple Couchbase versions since two years like this. The query is:

SELECT * FROM t USE KEYS $ts
LEFT NEST it ON ANY s IN it.sk SATISFIES s.sk IN t.sk[*].sk END
LEFT JOIN st ON META(st).id=t.st
LEFT NEST p ON META(p).id IN ARRAY IFMISSINGORNULL(s.pr.id, s.sk) FOR s IN t.sk END
LEFT NEST inv ON META(inv).id IN ARRAY META(i).id || '_s'  || t.st FOR i IN it END
LEFT JOIN te ON META(te).id IN IFMISSINGORNULL(t.te, IFMISSINGORNULL(ARRAY_IFNULL(it[*].te), IFMISSINGORNULL(s.te, 'default')))
LEFT JOIN ca ON META(ca).id = t.ca.id
LEFT JOIN t AS ma ON META(ma).id=t.ma

The error message we receive is:

{
“code”: 4000,
“msg”: "Sargable index not sarged; pred=any s in (it.sk) satisfies ((s.sk) in (array_star((t.sk)).sk)) end, sarg_keys=[(distinct (array (s.sk) for s in (it.sk) end))], error=Plan error: sarg.VisitAny: unexpected array id (0) for ANY expression any s in (it.sk) satisfies ((s.sk) in (array_star((t.sk)).sk)) end",
…
}

The index that is related to the issue is:

CREATE INDEX `idx_it_sk` ON `dp`.`d`.`it`((distinct (array (`s`.`sk`) for `s` in `sk` end)))

As soon as this index is deleted the query works, but of course without using the index which is not admired. Dropping and recreating did not solve the issue.
I can not find anything similar in the forum and can not wrap my head around what needs to be done to fix this. Help is highly appreciated.

2

It is MB-63163.
@Dhanya @vsr1

1 Like
3

Thanks, that’s exactly it. I can see that a build containing this fix has been created. Any chance to download this build somewhere?

4

Builds containing the fix haven’t been released yet.

You may be able to workaround it converting the LEFT NEST keyspace (with a condition) to nesting a sub-query, perhaps something like:

...
LEFT NEST (SELECT it.* FROM it WHERE ANY s IN it.sk SATISFIES s.sk IN t.sk[*].sk END) AS it ON true
...

HTH.

3 Likes
5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.