Nested EXCEPT does the wrong thing on empty results

N1QL
,
#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