Details
Minor
Description
The issue is also reproducible on TokuDB builds of MySQL-5.5.36 and MariaDB 5.5.36 (but not reproducible without TokuDB).
The result of SELECT with NOT IN ( .. NULL .. ) clause differs from the result of CREATE TABLE .. SELECT (with the same query).
Test case:
CREATE TABLE t1 (a CHAR(3), INDEX(a)) ENGINE=TokuDB;
INSERT INTO t1 VALUES ('foo'),( NULL);
SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 );
CREATE TABLE t2 AS
SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 );
SELECT * FROM t2;
Results (for MySQL-5.5.36-TokuDB):
MySQL [test]> SELECT @@version;
+---------------------+
| @@version |
+---------------------+
| 5.5.36-tokudb-7.1.5 |
+---------------------+
MySQL [test]> SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 ); Empty set (0.00 sec) MySQL [test]> CREATE TABLE t2 AS -> SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 ); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL [test]> SELECT * FROM t2; +------+ | a | +------+ | NULL | | foo | +------+ 2 rows in set (0.00 sec)
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
fixed in tokudb 7.1.6
TokuDB generates an empty result set for a select with sub-select, but generated a non-empty result set for the same query when creating a new table. The result set should be the same in both cases. What is going on here? InnoDB computes an empty result set for both cases.
I traced the problem to the tokudb::index_read function. The tokudb::index_read does not handle the null byte key prefix. Unfortunately, I do not understand how the null byte (byte 0) in the key handed to the ::index_read function is supposed to work. Most of the time, the value of this byte is 0. Sometimes it is set to 1. How should the storage engine work WRT this?
BTW, this problem exists with TokuDB in MySQL 5.5.36, MySQL 5.6.16. I have not been able to consistently see this on MariaDB 5.5.36 for some reason. In any case, I am interested in how the handler's index_read function is supposed to process the null byte.