Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.1.6
-
None
-
2019-06, 2020-1, 2020-2
Description
When an outer query column values is NULL and the subquery is returning an empty set, the outer query and the predicate is NULL, the row is supposed to be part of the result set. But the column store does not return such rows. For example: I got the following result from INNODB:
{{select t1.c1,t1.c2, t1.c3 from t1 where t1.c1 not in (select t2.c1 from t2 where t2.c2 >100)
--------------
------------------------
c1 | c2 | c3 |
------------------------
1 | 1 | char-row1-c3c4 |
NULL | NULL | char-row2 |
3 | 30 | NULL |
4 | 40 | char-row4-c3 |
5 | NULL | char-row5-c3 |
6 | 6 | char-row6-c3 |
NULL | 7 | NULL |
------------------------
Where for my column store "mydb" it produced the following result:
select t1.c1,t1.c2, t1.c3 from t1 where t1.c1 not in (select t2.c1 from t2 where t2.c2 >100)
--------------
------------------------
c1 | c2 | c3 |
------------------------
1 | 1 | char-row1-c3c4 |
3 | 30 | NULL |
4 | 40 | char-row4-c3 |
5 | NULL | char-row5-c3 |
6 | 6 | char-row6-c3 |
------------------------}}
To reproduce the problem:
create database mydb;
create database innodb;
run the bug2_setup.sql to create and load the tables.
bug2.sql contains the test sql query.