IN/NOT IN subquery returns incorrect results for some cases

create table cs1 (a int)engine=columnstore;

insert into cs1 values (1), (2), (3), (4), (null);

create table cs2 (b int, c int)engine=columnstore;

insert into cs2 values (1, 100), (1, 101), (2, 200),

(3, 300), (3, 301), (3, 302), (null, null);

MariaDB [test]> select * from cs1 where hex(a*10) not in (select hex(b*10) from cs2);

+------+

| a    |

+------+

|    4 |

+------+

1 row in set (0.073 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3)));

+------+

| a    |

+------+

|    2 |

|    3 |

|    4 |

| NULL |

+------+

4 rows in set (0.029 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a not in (2,3)));

Empty set (0.030 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b=3);

+------+

| a    |

+------+

|    1 |

|    2 |

|    4 |

| NULL |

+------+

4 rows in set (0.017 sec)

MariaDB [test]> select * from cs1 where a not in (select count(*) over (partition by b) from cs2 where b is null);

+------+

| a    |

+------+

|    2 |

|    3 |

|    4 |

| NULL |

+------+

4 rows in set (0.017 sec)

MariaDB [test]> select * from cs1 where a not in (select c from cs2 where b is not null);

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

| NULL |

+------+

5 rows in set (0.019 sec)

MariaDB [test]> select * from cs1 where (a+a) not in (select (b+b) from cs2 where b is not null);

+------+

| a    |

+------+

|    4 |

| NULL |

+------+

2 rows in set (0.024 sec)

MariaDB [test]> select * from cs1 where (a+1) not in (select b from cs2 where b is not null);

+------+

| a    |

+------+

|    3 |

|    4 |

| NULL |

+------+

3 rows in set (0.018 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3) and a is not null) and b is not null);

+------+

| a    |

+------+

|    2 |

|    3 |

|    4 |

| NULL |

+------+

4 rows in set (0.034 sec)

MariaDB [test]> select * from cs1 where hex(a*10) not in (select hex(b*10) from cs2);

Empty set (0.001 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3)));

+------+

| a    |

+------+

|    2 |

|    3 |

|    4 |

+------+

3 rows in set (0.001 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a not in (2,3)));

+------+

| a    |

+------+

|    1 |

|    4 |

+------+

2 rows in set (0.001 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b=3);

+------+

| a    |

+------+

|    1 |

|    2 |

|    4 |

+------+

3 rows in set (0.000 sec)

MariaDB [test]> select * from cs1 where a not in (select count(*) over (partition by b) from cs2 where b is null);

+------+

| a    |

+------+

|    2 |

|    3 |

|    4 |

+------+

3 rows in set (0.001 sec)

MariaDB [test]> select * from cs1 where a not in (select c from cs2 where b is not null);

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.000 sec)

MariaDB [test]> select * from cs1 where (a+a) not in (select (b+b) from cs2 where b is not null);

+------+

| a    |

+------+

|    4 |

+------+

1 row in set (0.000 sec)

MariaDB [test]> select * from cs1 where (a+1) not in (select b from cs2 where b is not null);

+------+

| a    |

+------+

|    3 |

|    4 |

+------+

2 rows in set (0.000 sec)

MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3) and a is not null) and b is not null);

+------+

| a    |

+------+

|    2 |

|    3 |

|    4 |

+------+

3 rows in set (0.001 sec)