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
| Field | Original Value | New Value |
|---|---|---|
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Fix Version/s | 1.2.6 [ 23734 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Roman [ drrtuy ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Roman [ drrtuy ] | Patrick LeBlanc [ pleblanc ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Patrick LeBlanc [ pleblanc ] | Roman [ drrtuy ] |
| Status | In Review [ 10002 ] | Needs Feedback [ 10501 ] |
| Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
| Fix Version/s | 1.4.2 [ 24023 ] |
| Sprint | 2019-06 [ 332 ] |
| Fix Version/s | 1.4.3 [ 24038 ] | |
| Fix Version/s | 1.4.2 [ 24023 ] |
| Fix Version/s | 1.4.2 [ 24023 ] | |
| Fix Version/s | 1.4.3 [ 24038 ] |
| Fix Version/s | 1.4.3 [ 24038 ] | |
| Fix Version/s | 1.4.2 [ 24023 ] |
| Assignee | Roman [ drrtuy ] | Andrew Hutchings [ linuxjedi ] |
| Status | Open [ 1 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | In Testing [ 10301 ] |
| Assignee | Andrew Hutchings [ linuxjedi ] | Daniel Lee [ dleeyh ] |
| Sprint | 2019-06 [ 332 ] | 2019-06, 2019-7 [ 332, 376 ] |
| Story Points | 1 |
| Sprint | 2019-06, 2020-1 [ 332, 376 ] | 2019-06, 2020-1, 2020-2 [ 332, 376, 382 ] |
| Fix Version/s | 1.2.6 [ 23734 ] |
| issue.field.resolutiondate | 2020-02-13 17:10:24.0 | 2020-02-13 17:10:24.48 |
| Resolution | Fixed [ 1 ] | |
| Status | In Testing [ 10301 ] | Closed [ 6 ] |
| Team | ColumnStore Team |
| Rank | Ranked higher |