Details
Minor
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.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Rich,
I don't see any test case in the mentioned commit. I can add a test case, but only after we merge in your fix. Alternatively I could send you a test case that you can just copy/paste in a .test file (e.g. subselect4.test, or some TokuDB-specific test file).
Let me know how do you prefer to proceed.
I am using your test case. See this commit: https://github.com/Tokutek/mariadb/commit/219f3382426ca84eb80fa23d70719ebd46736dab. I would be interested in any additional tests.
I don't have additional tests, these cover the cases I am aware of. Perhaps I could discover other execution strategies with the same problem, and make tests for them, but that would be a rather time-consuming exercise. Since you added the test case as well, I will close this bug.
Fixed in TokuDB. Test case added to TokuDB:
https://github.com/Tokutek/mariadb/commit/219f3382426ca84eb80fa23d70719ebd46736dab
A test case without a join:
CREATE TABLE t1 (a1 char(2), a2 char(2)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (NULL, 'xx');
CREATE TABLE t2t (b char(2), INDEX(b)) ENGINE=TokuDB;
CREATE TABLE t2i (b char(2), INDEX(b)) ENGINE=InnoDB;
INSERT INTO t2t VALUES ('yy'),('zz');
INSERT INTO t2i VALUES ('yy'),('zz');
SET optimizer_switch = 'materialization=on';
explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SET optimizer_switch = 'materialization=off';
explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);