MCS transforms (NOT) IN predicate into (NOT) EXISTS internally. GROUP BY doesn't set NOT properly so 1st and 3d cases have teh same reason. The second case is different since InSub::transform() calls getSelectPlan that lacks needed information in MariaDB structures. It's worth to note the server's optimizer makes condition push prior the GROUP BY handler creation. It must contain the needed info.
Please review.
The proposed fix solves 1st and 3d cases.
MCOL-1963 will fix the 2nd.
Build verified: 2018-11-26 nightly, Lentos 7 RPM
MariaDB [mytest]> create table cs1(i bigint) engine=columnstore;
Query OK, 0 rows affected (0.196 sec)
MariaDB [mytest]> create table cs2(i bigint) engine=columnstore;
Query OK, 0 rows affected (0.159 sec)
MariaDB [mytest]> insert into cs1 values (10),(20),(30);
Query OK, 3 rows affected (0.273 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [mytest]> insert into cs2 values (10),(21),(30);
Query OK, 3 rows affected (0.251 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [mytest]> set infinidb_vtable_mode=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [mytest]> select sum
from cs1 where cs1.i not in ( select i from cs2 );
--------
| sum |
--------
| 20 |
--------
1 row in set (0.101 sec)
MariaDB [mytest]> set infinidb_vtable_mode=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [mytest]> select sum from cs1 where cs1.i not in ( select i from cs2 );
--------
| sum |
--------
| 20 |
--------
1 row in set (0.052 sec)
MariaDB [mytest]> show tables;
------------------
| Tables_in_mytest |
------------------
| cs1 |
| cs2 |
| orders |
------------------
3 rows in set (0.001 sec)
MariaDB [mytest]> create table orderkeys (okey int) engine=columnstore;
Query OK, 0 rows affected (0.145 sec)
MariaDB [mytest]> insert into orderkeys select o_orderkey from orders where o_orderkey < 1000;
Query OK, 255 rows affected (1.290 sec)
Records: 255 Duplicates: 0 Warnings: 0
MariaDB [mytest]> select sum(okey) from orderkeys;
-----------
| sum(okey) |
-----------
| 127872 |
-----------
1 row in set (0.025 sec)
MariaDB [mytest]> select sum(o_orderkey) from orders;
-----------------
| sum(o_orderkey) |
-----------------
| 4499987250000 |
-----------------
1 row in set (0.045 sec)
MariaDB [mytest]> select sum(o_orderkey) from orders where o_orderkey not in (select okey from orderkeys);
-----------------
| sum(o_orderkey) |
-----------------
| 4499987122128 |
-----------------
1 row in set (1.309 sec)
MariaDB [mytest]> set infinidb_vtable_mode=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [mytest]> select sum(o_orderkey) from orders where o_orderkey not in (select okey from orderkeys);
-----------------
| sum(o_orderkey) |
-----------------
| 4499987122128 |
-----------------
1 row in set (0.173 sec)