Difference in results from sql vs n1ql:
|
|
Sql queries:
|
SELECT 3 not in (null);
|
|
Result : null
|
|
SELECT 3 in (null);
|
|
Result : null
|
|
SELECT * FROM simple_table_1 where primary_key_id=789 WHERE int_field1 NOT IN (query returning a null array) ;
|
Result: null
|
|
|
SELECT * FROM simple_table_1 where primary_key_id=789 WHERE int_field1 IN (query returning a null array) ;
|
Result: null
|
|
|
|
SELECT * FROM simple_table_1 t_3 USE KEYS "789" WHERE int_field1 NOT IN (query returning a null array) ;
|
Gives 1 result with key as 789.
|
Explain of above query gives keyscan.
|
|
Without use keys:
|
|
SELECT * FROM simple_table_1 t_3 WHERE int_field1 NOT IN (query returning a null array) ;
|
Explain of above query gives ValueScan.
|
|
Following scenario gives different results:
|
|
Create bucket default in couchbase
|
Create primary index on default.
|
|
N1QL Query issued:
|
select 3 in (select min(x) from default);
|
[
|
{
|
"$1": false
|
}
|
]
|
|
|
sql query issued for table test with no documents:
|
|
mysql> select 3 in (select min(x) from test);
|
+---------------------------------+
|
| 3 in (select min(x) from test) |
|
+---------------------------------+
|
| NULL |
|
+---------------------------------+
|
1 row in set (0.01 sec)
|
|
Result from oracle is attached.
It gives no rows for queries like:
select 4 from dual where 4 in (null);
select 4 from dual where 4 in (null,6);