[MCOL-1734] NOT IN subquery does not return rows with NULL qualifying column values Created: 2018-09-20  Updated: 2020-03-13  Resolved: 2020-02-13

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.1.6
Fix Version/s: 1.4.3

Type: Bug Priority: Major
Reporter: Ravi Prakash (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: File bug2.sql     File bug2_setup.sql    
Issue Links:
Issue split
split to MCOL-3737 NOT IN subquery does not return rows ... Closed
split to MCOL-3790 Clone of MCOL-1734 for 1.2.6 Closed
Problem/Incident
causes MCOL-3677 outer join query returned incorrect r... Closed
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
MCOL-3737 NOT IN subquery does not return rows ... Sub-Task Closed Roman  
Sprint: 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.

 Comments   
Comment by Ravi Prakash (Inactive) [ 2018-10-02 ]

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);
----------------------------------+

c2 c3 c1 c4

----------------------------------+

NULL char-row2 NULL char-row2-c4
30 NULL 35 NULL
NULL char-row5-c3 55 NULL
75 NULL NULL char-row7-c4 monospaced text
85 NULL NULL char-row8-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.

Comment by Roman [ 2019-12-06 ]

Plz review.

Comment by Patrick LeBlanc (Inactive) [ 2019-12-06 ]

Merged the code change.

This one should have a corresponding test in the regression suite though, so sending back to Roman.

Comment by Andrew Hutchings (Inactive) [ 2019-12-14 ]

Not including this in 1.4.2 release notes as it is still pending additions to regression suite.

Comment by Andrew Hutchings (Inactive) [ 2019-12-18 ]

Caused regression MCOL-3677 so has been reverted.

Comment by Roman [ 2020-01-09 ]

Plz review.

Comment by Daniel Lee (Inactive) [ 2020-02-13 ]

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

Generated at Thu Feb 08 02:30:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.