subquery NULL compatibility

[breezewish]

Description

MySQL:

mysql> drop table t1;
mysql> create table t1 (a varchar(20));
mysql> insert into t1 values ('A'),('BC'),('DEF');
mysql> select NULL IN (SELECT * from t1);
+----------------------------+
| NULL IN (SELECT * from t1) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> drop table t1;
mysql> create table t1 (a varchar(20));
mysql> insert into t1 values ('A'),('BC'),('DEF');
mysql> select NULL IN (SELECT * from t1);
mysql> select NULL IN (SELECT * from t1);
+----------------------------+
| NULL IN (SELECT * from t1) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

SIG slack channel

#sig-exec

Score

• 900

Mentor

• @lzmhhh123

[zz-jason]

another case:

drop table if exists subq;
drop table if exists t;
create table subq(a bigint);
create table t(a bigint, b bigint);
insert into subq (a) values(1), (2), (3), (null);
insert into t (a, b) values (null, 1), (2, null);

select a in (select * from subq) from t;
select b in (select * from subq) from t;
select null in (select * from subq) from t;
select 1 in (select * from subq) from t;
select 6 in (select * from subq) from t;

[zz-jason]

TiDB > select a in (select * from subq) from t;
+---------------------------+
| a in (select * from subq) |
+---------------------------+
|                      NULL |
|                         1 |
+---------------------------+
2 rows in set (0.00 sec)

this behaves the same as MySQL

TiDB > select null in (select * from subq) from t;
+------------------------------+
| null in (select * from subq) |
+------------------------------+
|                            0 |
|                            0 |
+------------------------------+
2 rows in set (0.00 sec)

TiDB > select 6 in (select * from subq) from t;
+---------------------------+
| 6 in (select * from subq) |
+---------------------------+
|                         0 |
|                         0 |
+---------------------------+
2 rows in set (0.00 sec)

in MySQL, the above two sql returns two NULL:

mysql> select null in (select * from subq) from t;
+------------------------------+
| null in (select * from subq) |
+------------------------------+
|                         NULL |
|                         NULL |
+------------------------------+
2 rows in set (0.00 sec)

mysql> select 6 in (select * from subq) from t;
+---------------------------+
| 6 in (select * from subq) |
+---------------------------+
|                      NULL |
|                      NULL |
+---------------------------+
2 rows in set (0.00 sec)

the IN sub-query is built as a semi-join in TiDB:

TiDB > explain select null in (select * from subq) from t;
+----------------+----------------+------+-------------------------------------------------+
| id             | parents        | task | operator info                                   |
+----------------+----------------+------+-------------------------------------------------+
| TableScan_9    |                | cop  | table:t, range:(-inf,+inf), keep order:false    |
| TableReader_10 | HashSemiJoin_8 | root | data:TableScan_9                                |
| TableScan_11   |                | cop  | table:subq, range:(-inf,+inf), keep order:false |
| TableReader_12 | Selection_7    | root | data:TableScan_11                               |
| Selection_7    | HashSemiJoin_8 | root | eq(null, cast(test.subq.a))                     |
| HashSemiJoin_8 |                | root | right:Selection_7, aux                          |
+----------------+----------------+------+-------------------------------------------------+
6 rows in set (0.00 sec)

TiDB > explain select 6 in (select * from subq) from t;
+----------------+----------------+------+-------------------------------------------------+
| id             | parents        | task | operator info                                   |
+----------------+----------------+------+-------------------------------------------------+
| TableScan_8    |                | cop  | table:t, range:(-inf,+inf), keep order:false    |
| TableReader_9  | HashSemiJoin_7 | root | data:TableScan_8                                |
| TableScan_10   | Selection_11   | cop  | table:subq, range:(-inf,+inf), keep order:false |
| Selection_11   |                | cop  | eq(6, test.subq.a)                              |
| TableReader_12 | HashSemiJoin_7 | root | data:Selection_11                               |
| HashSemiJoin_7 |                | root | right:TableReader_12, aux                       |
+----------------+----------------+------+-------------------------------------------------+
6 rows in set (0.01 sec)

to fix this issue, we have to:

• do not extract on conditions composed by a column and constant to left or right table filter conditions in function extractOnCondition
• support constant join keys

[morgo]

Confirming that this can still be reproduced:

tidb> drop table t1;
Query OK, 0 rows affected (0.02 sec)

tidb> create table t1 (a varchar(20));
Query OK, 0 rows affected (0.02 sec)

tidb> insert into t1 values ('A'),('BC'),('DEF');
Query OK, 3 rows affected (0.00 sec)

tidb> select NULL IN (SELECT * from t1);
+----------------------------+
| NULL IN (SELECT * from t1) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-rc.3-219-g1e0876fe8-dirty
Git Commit Hash: 1e0876fe810a832721aac52275dd2b7792fd2892
Git Branch: flush
UTC Build Time: 2018-11-24 01:12:47
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

[wwar]

No table required, the easiest test-case for this is:

SELECT NULL IN (SELECT NULL);

TiDB returns a bool (FALSE) versus NULL in MySQL.

[ghost]

An example to show how this could manifest into bugs:

mysql> SELECT NOT NULL IN (SELECT NULL);
+---------------------------+
| NOT NULL IN (SELECT NULL) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

This should return NULL.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

v5.0.0-rc

[ti-srebot]

( AffectedVersions ) fields are empty.
The values in ( FixedVersions ) fields are incorrect.