Description:
The same SELECT statement returned different results after I added a NULL record.
SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0)));
c1|c0|c1|
--+--+--+
8|8w| |
REPLACE INTO t0(c1) VALUES(NULL);
SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0)));
Empty set;
Additional,after changing the table engine to InnoDB, they both returned Empty set.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `t0` ( `c0` bigint , `c1` decimal(10,0), UNIQUE KEY `c1` (`c1`)) ENGINE=MEMORY ;
CREATE TABLE `t1` ( `c0` varchar(500) , `c1` int ) ENGINE = MEMORY;
REPLACE INTO t1(c0) VALUES("8w");
INSERT IGNORE INTO t0(c1, c0) VALUES("8&dL", NULL);
SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0)));
REPLACE INTO t0(c1) VALUES(NULL);
SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0)));
Description: The same SELECT statement returned different results after I added a NULL record. SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0))); c1|c0|c1| --+--+--+ 8|8w| | REPLACE INTO t0(c1) VALUES(NULL); SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0))); Empty set; Additional,after changing the table engine to InnoDB, they both returned Empty set. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE `t0` ( `c0` bigint , `c1` decimal(10,0), UNIQUE KEY `c1` (`c1`)) ENGINE=MEMORY ; CREATE TABLE `t1` ( `c0` varchar(500) , `c1` int ) ENGINE = MEMORY; REPLACE INTO t1(c0) VALUES("8w"); INSERT IGNORE INTO t0(c1, c0) VALUES("8&dL", NULL); SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0))); REPLACE INTO t0(c1) VALUES(NULL); SELECT t0.c1, t1.c0, t1.c1 FROM t1, t0 WHERE (NOT ((t0.c1) NOT IN (t1.c0)));