Description:
Two sql supposed to return the same result, but they don't.
SET optimizer_switch='default';
SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
+------+
4 rows in set (0.01 sec)
SET optimizer_switch='subquery_to_derived=on';
SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
10 rows in set (0.00 sec)
These two select sql are the same, but the results they return are different.
How to repeat:
CREATE TABLE `info` (
`id` text
);
CREATE TABLE `person` (
`id` tinyint(3) unsigned zerofill,
`name` float DEFAULT NULL,
UNIQUE KEY `id` (`id`)
);
INSERT INTO `info` VALUES ('2929042'),(''),(NULL),('IcK5A'),(NULL),('z)]s'),('1231dba'),('4f*T');
INSERT INTO `person` VALUES (89,NULL),(NULL,0),(NULL,2929042),(0,NULL),(NULL,NULL);
SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
+------+
4 rows in set (0.01 sec)
SET optimizer_switch='subquery_to_derived=on';
SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
10 rows in set (0.00 sec)
I run my sql test on containers from docker. I pull the docker images from docker hub, and run the sql above on the docker container.
Description: Two sql supposed to return the same result, but they don't. SET optimizer_switch='default'; SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id); +------+ | ref0 | +------+ | NULL | | NULL | | NULL | | NULL | +------+ 4 rows in set (0.01 sec) SET optimizer_switch='subquery_to_derived=on'; SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id); +------+ | ref0 | +------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +------+ 10 rows in set (0.00 sec) These two select sql are the same, but the results they return are different. How to repeat: CREATE TABLE `info` ( `id` text ); CREATE TABLE `person` ( `id` tinyint(3) unsigned zerofill, `name` float DEFAULT NULL, UNIQUE KEY `id` (`id`) ); INSERT INTO `info` VALUES ('2929042'),(''),(NULL),('IcK5A'),(NULL),('z)]s'),('1231dba'),('4f*T'); INSERT INTO `person` VALUES (89,NULL),(NULL,0),(NULL,2929042),(0,NULL),(NULL,NULL); SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id); +------+ | ref0 | +------+ | NULL | | NULL | | NULL | | NULL | +------+ 4 rows in set (0.01 sec) SET optimizer_switch='subquery_to_derived=on'; SELECT person.id AS ref0 FROM person LEFT OUTER JOIN info ON person.name = info.id WHERE (person.name IN (SELECT info.id AS ref1 FROM info )) OR (info.id); +------+ | ref0 | +------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +------+ 10 rows in set (0.00 sec) I run my sql test on containers from docker. I pull the docker images from docker hub, and run the sql above on the docker container.