Details
-
New Feature
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
None
-
None
-
None
Description
create table t1 (a int)engine=columnstore; |
create table t2 (a int)engine=columnstore; |
create table t3 (a int)engine=columnstore; |
insert into t1 values (1), (2), (3); |
insert into t2 values (2), (3); |
insert into t3 values (3); |
The following query produces expected result:
MariaDB [test]> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a; |
+------+------+------+ |
| a | a | a |
|
+------+------+------+ |
| 1 | NULL | NULL | |
| 2 | NULL | NULL | |
| 3 | 3 | 3 |
|
+------+------+------+ |
However, if we apply an 'is null' WHERE predicate to the above query, we get incorrect result:
MariaDB [test]> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a where t2.a is null; |
+------+------+------+ |
| a | a | a |
|
+------+------+------+ |
| 1 | NULL | NULL | |
| 2 | NULL | NULL | |
| 3 | NULL | NULL | |
+------+------+------+ |
The expected result is the below (here, i1, i2, i3 are equivalent InnoDB tables):
MariaDB [test]> select * from i1 left join i2 left join i3 on i2.a=i3.a on i1.a=i3.a where i2.a is null; |
+------+------+------+ |
| a | a | a |
|
+------+------+------+ |
| 1 | NULL | NULL | |
| 2 | NULL | NULL | |
+------+------+------+ |