Details
Description
Created 2 buckets b0 and b1 each with 3 documents like “1”:
{ “id”:”1”, “int_field”:1}, “2”:
{ “id”:”2”, “int_field”:2},“3”:
{ “id”:”3”, “int_field”:3}Generated primary indexes b0 and b1 and run the following query:
cbq> select * from b0 where EXISTS (select * from b1 USE KEYS Meta(b0).id);
{
"requestID": "3f48d90a-5088-46b5-8f9e-dbff27378187",
"signature":
,
"results": [
{
"b0":
},
{
"b0":
},
{
"b0":
}
],
"status": "success",
"metrics":
}
When we add the WHERE clause the result is different and depend on the condition given in WHERE
cbq> select * from b0 where EXISTS (select * from b1 USE KEYS Meta(b0).id where b1.int_field = 2);
{
"requestID": "59400eb6-3e14-4a4e-89bf-0897e1e69e53",
"signature":
,
"results": [
{
"b0":
}
],
"status": "success",
"metrics":
}
cbq>
This however in SQL will result in all 3 rows being read.
Why is this different in N1QL Vs SQL?
A similar reasoning will apply to NOT EXISTS
Basically, whatever is the sub-query outcome, outer results will depend on it. Meanwhile, in SQL inner query will result in similar results, but the net outcome is different.
Around 60% of tests are broken in sub-queries