Description:
MySQL incorrectly returns rows when using IN (IFNULL(<arg1>, <arg2>)) in a JOIN condition, even when no matching values exist in the joined table.
Eg:
Query 1:
mysql [localhost:8042] {msandbox} (database41) > SELECT DISTINCT
t1.c0 AS ref0,
t0.c0 AS ref1
FROM t0
INNER JOIN t1
ON (t1.c0) IN (
IFNULL('0.3792086453751513', "qR\r")
);
+-------------------------------------+-------------+
| ref0 | ref1 |
+-------------------------------------+-------------+
| 00000000000000000000000000000000000 | -539730000 |
| 00000000000000000000000000000000000 | 0 |
| 00000000000000000000000000000000000 | 1500280000 |
| 00000000000000000000000000000000000 | 0.112448 |
| 00000000000000000000000000000000000 | -1822370000 |
| 00000000000000000000000000000000000 | 0.861014 |
| 00000000000000000000000000000000000 | -767464000 |
| 00000000000000000000000000000000000 | 1280360000 |
| 00000000000000000000000000000000000 | 991116000 |
| 00000000000000000000000000000000000 | 0.383531 |
| 00000000000000000000000000000000000 | 767095000 |
| 00000000000000000000000000000000000 | 0.673761 |
| 00000000000000000000000000000000000 | -1496060000 |
+-------------------------------------+-------------+
13 rows in set (0.00 sec)
As you can see in the t1 table, the value 0.3792086453751513 does not exist in column c0, yet it is still being matched and returning incorrect results.
mysql [localhost:8042] {msandbox} (database41) > select * from t1;
+-------------------------------------+
| c0 |
+-------------------------------------+
| 00000000000000000000000000000000001 |
| 00000000000000000000000000000000001 |
| 00000000000000000000000000000000001 |
| 00000000000000000000000000000000001 |
| 00000000000000000000000000000000000 |
| 00000000000000000000000000000000000 |
+-------------------------------------+
6 rows in set (0.00 sec)
Query 2:
mysql [localhost:8042] {msandbox} (database41) > (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1
FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r"))
UNION
SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1
FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r")))
EXCEPT
(
(SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1
FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r"))
EXCEPT
SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1
FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r")))
UNION
(SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1
FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r"))
EXCEPT
SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1
FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r")))
);
Empty set (0.01 sec)
It appears to be an optimizer defect that should be thoroughly checked and tested.
How to repeat:
DROP DATABASE IF EXISTS database41;
CREATE DATABASE database41;
USE database41;
CREATE TABLE t0(c0 FLOAT COMMENT 'asdf' NOT NULL) ;
CREATE TABLE t1(c0 SMALLINT(35) ZEROFILL NOT NULL) INSERT_METHOD = LAST, DELAY_KEY_WRITE = 0;
INSERT INTO t1(c0) VALUES(0.1824230226382254);
INSERT IGNORE INTO t1(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES(-1669661514);
INSERT INTO t1(c0) VALUES(0.7472309988598856);
DELETE QUICK IGNORE FROM t1 WHERE (((t1.c0) AND (t1.c0)) IN (CAST(1777329468 AS SIGNED))) && (2013524628);
INSERT IGNORE INTO t1(c0) VALUES(963834148);
REPLACE INTO t0(c0) VALUES(1239037716);
REPLACE INTO t1(c0) VALUES(0.43602613101159104);
INSERT IGNORE INTO t1(c0) VALUES("'>");
INSERT IGNORE INTO t1(c0) VALUES(0.9934674085398288);
REPLACE INTO t0(c0) VALUES(0.6740703907035186);
INSERT IGNORE INTO t1(c0) VALUES(NULL);
REPLACE INTO t0(c0) VALUES(-1.769451152E9);
REPLACE INTO t1(c0) VALUES(0.4749621371179693);
REPLACE INTO t0(c0) VALUES(-30021013);
INSERT IGNORE INTO t0(c0) VALUES(0.24731106863745167), (1778147985), (0.09838507952625908), ('0.4660497943510168'), (0.8122674816254841);
INSERT IGNORE INTO t0(c0) VALUES(-1632610407);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES(2081612921);
INSERT INTO t0(c0) VALUES(0.990338853192909);
# UPDATE t1 SET c0=0;
REPLACE INTO t1(c0) VALUES(0.8431550115185268);
INSERT IGNORE INTO t0(c0) VALUES(1754857090), (NULL), ("");
INSERT IGNORE INTO t1(c0) VALUES('i6棘^T');
INSERT INTO t1(c0) VALUES(0.18962959651986877);
INSERT IGNORE INTO t1(c0) VALUES(549730166);
# ALTER TABLE t1 FORCE, PACK_KEYS 0;
REPLACE INTO t0(c0) VALUES(0.189489284896317), (0.851502970255646), (0.2104462311614259);
REPLACE INTO t0(c0) VALUES(-2.086789819E9);
INSERT IGNORE INTO t0(c0) VALUES(0.1841282621952196);
REPLACE INTO t0(c0) VALUES(-1130577659);
REPLACE INTO t1(c0) VALUES(0.15226799749882614);
INSERT INTO t0(c0) VALUES(-2054644770);
INSERT IGNORE INTO t0(c0) VALUES(1440616587);
INSERT IGNORE INTO t0(c0) VALUES(-85986860), ('Wf}R'), (876191032);
INSERT IGNORE INTO t0(c0) VALUES(0.27867858994878725);
CREATE INDEX i0 ON t1(c0 DESC);
INSERT IGNORE INTO t0(c0) VALUES(NULL), (NULL), ('OZDᛁ');
REPLACE INTO t0(c0) VALUES(-1667117131);
REPLACE INTO t0(c0) VALUES('-1197856791');
INSERT IGNORE INTO t1(c0) VALUES('65651547');
REPLACE INTO t0(c0) VALUES("0.942870114792822");
INSERT IGNORE INTO t1(c0) VALUES(1951500315);
REPLACE INTO t1(c0) VALUES(0.5509397793910037);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(0.4770861185062949);
INSERT IGNORE INTO t0(c0) VALUES(0.5715570769353812);
INSERT IGNORE INTO t1(c0) VALUES(-1920324395);
REPLACE INTO t1(c0) VALUES(0.025000575724580476);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t0(c0) VALUES(0.31210399912364317);
INSERT INTO t0(c0) VALUES(1818547475);
REPLACE INTO t0(c0) VALUES(-1979499865);
REPLACE INTO t0(c0) VALUES(0.10764013051980781), (-1766427762), (0.8988106227622641);
INSERT IGNORE INTO t1(c0) VALUES(NULL);
# CHECKSUM TABLE t0;
INSERT INTO t1(c0) VALUES(0.07064190860033626);
UPDATE t1 SET c0= EXISTS (SELECT 1) WHERE t1.c0;
INSERT INTO t1(c0) VALUES(0.5447848109638285);
INSERT IGNORE INTO t1(c0) VALUES(0.1560826941734691);
REPLACE INTO t0(c0) VALUES(332832373);
INSERT IGNORE INTO t0(c0) VALUES(0.28772261646954056);
INSERT IGNORE INTO t1(c0) VALUES(0.6913662847041423);
INSERT IGNORE INTO t1(c0) VALUES(0.8219073663240016);
UPDATE t0 SET c0='-539730446';
INSERT IGNORE INTO t0(c0) VALUES('');
DELETE LOW_PRIORITY FROM t1;
INSERT IGNORE INTO t1(c0) VALUES(1197056235);
INSERT IGNORE INTO t0(c0) VALUES(1500279666);
INSERT INTO t0(c0) VALUES("0.11244760007773402");
REPLACE INTO t0(c0) VALUES(-1.822372953E9);
INSERT IGNORE INTO t0(c0) VALUES('');
REPLACE INTO t0(c0) VALUES(0.8610140574800775);
# ALTER TABLE t0 STATS_AUTO_RECALC 1;
INSERT IGNORE INTO t1(c0) VALUES('&MﲶI');
REPLACE INTO t0(c0) VALUES('-767464133');
INSERT IGNORE INTO t1(c0) VALUES('[_'), (NULL), (65651547);
REPLACE INTO t0(c0) VALUES(1280356003);
DELETE LOW_PRIORITY QUICK FROM t1;
REPLACE INTO t1(c0) VALUES(0.5705589591860675);
INSERT IGNORE INTO t0(c0) VALUES('');
REPLACE INTO t0(c0) VALUES(991115748);
REPLACE INTO t1(c0) VALUES(0.7737006902214526), (0.8520837366965166), (0.016662214021319732);
REPLACE INTO t0(c0) VALUES(0.3835309206587264);
INSERT IGNORE INTO t1(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(0.8736937294158336);
REPLACE INTO t0(c0) VALUES(767094793);
REPLACE INTO t0(c0) VALUES("0.6737605706328825");
INSERT IGNORE INTO t0(c0) VALUES('-1496062311');
-- cardinality: 0
(SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"))) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"))));
-- cardinality: 13
SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"));
Description: MySQL incorrectly returns rows when using IN (IFNULL(<arg1>, <arg2>)) in a JOIN condition, even when no matching values exist in the joined table. Eg: Query 1: mysql [localhost:8042] {msandbox} (database41) > SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN ( IFNULL('0.3792086453751513', "qR\r") ); +-------------------------------------+-------------+ | ref0 | ref1 | +-------------------------------------+-------------+ | 00000000000000000000000000000000000 | -539730000 | | 00000000000000000000000000000000000 | 0 | | 00000000000000000000000000000000000 | 1500280000 | | 00000000000000000000000000000000000 | 0.112448 | | 00000000000000000000000000000000000 | -1822370000 | | 00000000000000000000000000000000000 | 0.861014 | | 00000000000000000000000000000000000 | -767464000 | | 00000000000000000000000000000000000 | 1280360000 | | 00000000000000000000000000000000000 | 991116000 | | 00000000000000000000000000000000000 | 0.383531 | | 00000000000000000000000000000000000 | 767095000 | | 00000000000000000000000000000000000 | 0.673761 | | 00000000000000000000000000000000000 | -1496060000 | +-------------------------------------+-------------+ 13 rows in set (0.00 sec) As you can see in the t1 table, the value 0.3792086453751513 does not exist in column c0, yet it is still being matched and returning incorrect results. mysql [localhost:8042] {msandbox} (database41) > select * from t1; +-------------------------------------+ | c0 | +-------------------------------------+ | 00000000000000000000000000000000001 | | 00000000000000000000000000000000001 | | 00000000000000000000000000000000001 | | 00000000000000000000000000000000001 | | 00000000000000000000000000000000000 | | 00000000000000000000000000000000000 | +-------------------------------------+ 6 rows in set (0.00 sec) Query 2: mysql [localhost:8042] {msandbox} (database41) > (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r"))) EXCEPT ( (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\t\r"))) ); Empty set (0.01 sec) It appears to be an optimizer defect that should be thoroughly checked and tested. How to repeat: DROP DATABASE IF EXISTS database41; CREATE DATABASE database41; USE database41; CREATE TABLE t0(c0 FLOAT COMMENT 'asdf' NOT NULL) ; CREATE TABLE t1(c0 SMALLINT(35) ZEROFILL NOT NULL) INSERT_METHOD = LAST, DELAY_KEY_WRITE = 0; INSERT INTO t1(c0) VALUES(0.1824230226382254); INSERT IGNORE INTO t1(c0) VALUES(NULL); INSERT IGNORE INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES(-1669661514); INSERT INTO t1(c0) VALUES(0.7472309988598856); DELETE QUICK IGNORE FROM t1 WHERE (((t1.c0) AND (t1.c0)) IN (CAST(1777329468 AS SIGNED))) && (2013524628); INSERT IGNORE INTO t1(c0) VALUES(963834148); REPLACE INTO t0(c0) VALUES(1239037716); REPLACE INTO t1(c0) VALUES(0.43602613101159104); INSERT IGNORE INTO t1(c0) VALUES("'>"); INSERT IGNORE INTO t1(c0) VALUES(0.9934674085398288); REPLACE INTO t0(c0) VALUES(0.6740703907035186); INSERT IGNORE INTO t1(c0) VALUES(NULL); REPLACE INTO t0(c0) VALUES(-1.769451152E9); REPLACE INTO t1(c0) VALUES(0.4749621371179693); REPLACE INTO t0(c0) VALUES(-30021013); INSERT IGNORE INTO t0(c0) VALUES(0.24731106863745167), (1778147985), (0.09838507952625908), ('0.4660497943510168'), (0.8122674816254841); INSERT IGNORE INTO t0(c0) VALUES(-1632610407); INSERT IGNORE INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES(2081612921); INSERT INTO t0(c0) VALUES(0.990338853192909); # UPDATE t1 SET c0=0; REPLACE INTO t1(c0) VALUES(0.8431550115185268); INSERT IGNORE INTO t0(c0) VALUES(1754857090), (NULL), (""); INSERT IGNORE INTO t1(c0) VALUES('i6棘^T'); INSERT INTO t1(c0) VALUES(0.18962959651986877); INSERT IGNORE INTO t1(c0) VALUES(549730166); # ALTER TABLE t1 FORCE, PACK_KEYS 0; REPLACE INTO t0(c0) VALUES(0.189489284896317), (0.851502970255646), (0.2104462311614259); REPLACE INTO t0(c0) VALUES(-2.086789819E9); INSERT IGNORE INTO t0(c0) VALUES(0.1841282621952196); REPLACE INTO t0(c0) VALUES(-1130577659); REPLACE INTO t1(c0) VALUES(0.15226799749882614); INSERT INTO t0(c0) VALUES(-2054644770); INSERT IGNORE INTO t0(c0) VALUES(1440616587); INSERT IGNORE INTO t0(c0) VALUES(-85986860), ('Wf}R'), (876191032); INSERT IGNORE INTO t0(c0) VALUES(0.27867858994878725); CREATE INDEX i0 ON t1(c0 DESC); INSERT IGNORE INTO t0(c0) VALUES(NULL), (NULL), ('OZDᛁ'); REPLACE INTO t0(c0) VALUES(-1667117131); REPLACE INTO t0(c0) VALUES('-1197856791'); INSERT IGNORE INTO t1(c0) VALUES('65651547'); REPLACE INTO t0(c0) VALUES("0.942870114792822"); INSERT IGNORE INTO t1(c0) VALUES(1951500315); REPLACE INTO t1(c0) VALUES(0.5509397793910037); INSERT IGNORE INTO t0(c0) VALUES(NULL); REPLACE INTO t1(c0) VALUES(0.4770861185062949); INSERT IGNORE INTO t0(c0) VALUES(0.5715570769353812); INSERT IGNORE INTO t1(c0) VALUES(-1920324395); REPLACE INTO t1(c0) VALUES(0.025000575724580476); INSERT IGNORE INTO t0(c0) VALUES(NULL); REPLACE INTO t0(c0) VALUES(0.31210399912364317); INSERT INTO t0(c0) VALUES(1818547475); REPLACE INTO t0(c0) VALUES(-1979499865); REPLACE INTO t0(c0) VALUES(0.10764013051980781), (-1766427762), (0.8988106227622641); INSERT IGNORE INTO t1(c0) VALUES(NULL); # CHECKSUM TABLE t0; INSERT INTO t1(c0) VALUES(0.07064190860033626); UPDATE t1 SET c0= EXISTS (SELECT 1) WHERE t1.c0; INSERT INTO t1(c0) VALUES(0.5447848109638285); INSERT IGNORE INTO t1(c0) VALUES(0.1560826941734691); REPLACE INTO t0(c0) VALUES(332832373); INSERT IGNORE INTO t0(c0) VALUES(0.28772261646954056); INSERT IGNORE INTO t1(c0) VALUES(0.6913662847041423); INSERT IGNORE INTO t1(c0) VALUES(0.8219073663240016); UPDATE t0 SET c0='-539730446'; INSERT IGNORE INTO t0(c0) VALUES(''); DELETE LOW_PRIORITY FROM t1; INSERT IGNORE INTO t1(c0) VALUES(1197056235); INSERT IGNORE INTO t0(c0) VALUES(1500279666); INSERT INTO t0(c0) VALUES("0.11244760007773402"); REPLACE INTO t0(c0) VALUES(-1.822372953E9); INSERT IGNORE INTO t0(c0) VALUES(''); REPLACE INTO t0(c0) VALUES(0.8610140574800775); # ALTER TABLE t0 STATS_AUTO_RECALC 1; INSERT IGNORE INTO t1(c0) VALUES('&MﲶI'); REPLACE INTO t0(c0) VALUES('-767464133'); INSERT IGNORE INTO t1(c0) VALUES('[_'), (NULL), (65651547); REPLACE INTO t0(c0) VALUES(1280356003); DELETE LOW_PRIORITY QUICK FROM t1; REPLACE INTO t1(c0) VALUES(0.5705589591860675); INSERT IGNORE INTO t0(c0) VALUES(''); REPLACE INTO t0(c0) VALUES(991115748); REPLACE INTO t1(c0) VALUES(0.7737006902214526), (0.8520837366965166), (0.016662214021319732); REPLACE INTO t0(c0) VALUES(0.3835309206587264); INSERT IGNORE INTO t1(c0) VALUES(NULL); INSERT IGNORE INTO t0(c0) VALUES(NULL); REPLACE INTO t1(c0) VALUES(0.8736937294158336); REPLACE INTO t0(c0) VALUES(767094793); REPLACE INTO t0(c0) VALUES("0.6737605706328825"); INSERT IGNORE INTO t0(c0) VALUES('-1496062311'); -- cardinality: 0 (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"))) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r")))); -- cardinality: 13 SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR \r"));