Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.5.34, 10.0.6
-
None
Description
I set it to Minor because the combination of pre-conditions (NOT IN subquery with two values in the left expression and with NULLs, non-default materialization=off, TokuDB) makes it a corner case.
Test case:
CREATE TABLE t1 (a INT) ENGINE=TokuDB; |
INSERT INTO t1 VALUES (NULL),(3); |
|
|
CREATE TABLE t2 (b INT, INDEX(b)) ENGINE=TokuDB; |
INSERT INTO t2 VALUES (4),(5); |
|
|
SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 ); |
|
|
SET optimizer_switch = 'materialization=off'; |
|
|
SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 ); |
The semantics of this NOT NULL is a bit vague for me here, but considering the following
MariaDB [test]> select (null, null) not in (select 4, 4 union select 5, 5);
|
+-----------------------------------------------------+
|
| (null, null) not in (select 4, 4 union select 5, 5) |
|
+-----------------------------------------------------+
|
| NULL |
|
+-----------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select (null, 3) not in (select 4, 4 union select 5, 5);
|
+--------------------------------------------------+
|
| (null, 3) not in (select 4, 4 union select 5, 5) |
|
+--------------------------------------------------+
|
| 1 |
|
+--------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select (3, null) not in (select 4, 4 union select 5, 5);
|
+--------------------------------------------------+
|
| (3, null) not in (select 4, 4 union select 5, 5) |
|
+--------------------------------------------------+
|
| 1 |
|
+--------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select (3, 3) not in (select 4, 4 union select 5, 5);
|
+-----------------------------------------------+
|
| (3, 3) not in (select 4, 4 union select 5, 5) |
|
+-----------------------------------------------+
|
| 1 |
|
+-----------------------------------------------+
|
1 row in set (0.00 sec)
|
I presume the correct result is the one that is returned with materialization=on:
MariaDB [test]> SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 ); |
+------+------+ |
| a | a |
|
+------+------+ |
| 3 | NULL | |
| NULL | 3 | |
| 3 | 3 |
|
+------+------+ |
3 rows in set (0.01 sec) |
and the result with materialization=off is wrong:
MariaDB [test]> SET optimizer_switch = 'materialization=off'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 ); |
+------+------+ |
| a | a |
|
+------+------+ |
| 3 | 3 |
|
+------+------+ |
1 row in set (0.00 sec) |
Also reproducible on mariadb-5.5.30-tokudb-7.0.1-linux-x86_64, but not on mysql-5.5.30-tokudb-7.1.0-linux-x86_64.
Could not reproduce with InnoDB or MyISAM.