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.
Attachments
Issue Links
Activity
Merged the code change.
This one should have a corresponding test in the regression suite though, so sending back to Roman.
Not including this in 1.4.2 release notes as it is still pending additions to regression suite.
Build verified: 1.2.6-1 source
server
commit d3294cf9c18663673c62532dddaa11369ba184d0
engine
commit afea63b9a499ef7bd9776132526deeb8e84c8980
Build verified: 1.4.3-1 BB nightly
engine commit:
8588678
Reproduced the issue in 1.2.0-1.
The fix is in 1.4.3-1, but not 1.2.6-1
Clone the ticket MCOL-3790 to track the issue in 1.2.6-1. Closing this ticket for 1.4.3-1
There is one more scenario where the column store is not processing the NOT IN queries correctly. It does not match the title but the query is as follows:
MariaDB [mydb]> select t1.c2, t1.c3,t1.c1 ,t1.c4 from t1 where t1.c3 not in (select t2.c3 from t2 where t2.c2= t2.c2 and t2.c4 = t1.c4);
-----
-----------------------------+-----
-----------------------------+-----
-----------------------------+5 rows in set (0.12 sec)
It is returning an extra tuple : (75, null, null, char-row7-c4).
It is returning an extra tuple when the subquery is returning a lonely null value and the column value (t1.c3 in the above query) is also null.