[#MDEV-5932] Wrong result on CREATE TABLE .. AS SELECT .. <TokuDB table>

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)

[MDEV-5932] Wrong result on CREATE TABLE .. AS SELECT .. <TokuDB table> Created: 2014-03-22 Updated: 2014-05-06 Resolved: 2014-05-06
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.36, 10.0.9
Fix Version/s:	5.5.38, 10.0.11