RQG: Difference in subquery results(n1ql vs sql vs oracle) with not in/in (null)

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)