Nested EXCEPT does the wrong thing on empty results

SQL++
,
1

I have a query of this form:

  SELECT models.oid
  FROM models
  WHERE _type='Task' AND
  status NOT IN ['complete', 'removed'] AND 
  creator.`$ref`='wm'

  EXCEPT

  SELECT fromRef.oid
    (complex situation)

This query has two pieces. Each work separately correctly. Trouble is, when the second EXCEPT part returns a legitimate empty answer [], the whole query is broken.

Suppose the first query returns:

[ {oid: 'A' }, { oid: 'B' } ]

Now, in the case where the second query returns [] the result of the whole thing is []! That’s the issue.

In the case where the second query returns [ {oid: 'A' } ] then you get the result you expect, which is [ { oid: 'B' } ].

What am I missing here?

For extra weirdness, if I take out the second part and reformulate it to just be SELECT [] from models then the whole query works.

2

What version of Couchbase, and can you post the EXPLAIN output.

3

Community 4.0 – but I can’t explain output because the second part is complex, and gives away some details that I would rather not. The second part does involve a join back to the same bucket, and a complex condition which may fail. E.g. join models back to itself, and in that second joined model, check for the existence of a set of properties, some of which may or may not exist.

4

Looks like you are hitting https://issues.couchbase.com/browse/MB-16419

2 Likes
5

You can use UNION ALL to add a dummy record to the second term, so it will never be empty.

1 Like
6

Whoa … that does indeed look like just it. And it appears already fixed in version 4.1. Thanks for the quick and accurate answer.

7

Confirmed this work-around does work:

SELECT (complex stuff resulting in empty set) FROM models
UNION ALL (SELECT [] FROM models)
1 Like