Description:
CREATE TABLE `t0` (
`c0` text
);
CREATE TABLE `t1` (
`c0` tinyint(3) unsigned zerofill,
`c1` float DEFAULT NULL,
UNIQUE KEY `c0` (`c0`)
);
INSERT INTO `t0` VALUES ('292269042'),(''),(NULL),('IcK5A'),(NULL),('z)]s'),('292269042'),('4f*T');
INSERT INTO `t1` VALUES (255,NULL),(NULL,0),(NULL,292269000),(000,NULL),(NULL,NULL);
mysql> SET optimizer_switch='subquery_to_derived=on';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )) OR (t0.c0);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
9 rows in set (0.00 sec)
mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 ));
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t0.c0);
Empty set (0.00 sec)
I think `SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )) OR (t0.c0);` should contains 3 tuples as the sum of `SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t0.c0);` and `SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 ))`, but it dose not.
How to repeat:
Run the above sql.
Description: CREATE TABLE `t0` ( `c0` text ); CREATE TABLE `t1` ( `c0` tinyint(3) unsigned zerofill, `c1` float DEFAULT NULL, UNIQUE KEY `c0` (`c0`) ); INSERT INTO `t0` VALUES ('292269042'),(''),(NULL),('IcK5A'),(NULL),('z)]s'),('292269042'),('4f*T'); INSERT INTO `t1` VALUES (255,NULL),(NULL,0),(NULL,292269000),(000,NULL),(NULL,NULL); mysql> SET optimizer_switch='subquery_to_derived=on'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )) OR (t0.c0); +------+ | ref0 | +------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +------+ 9 rows in set (0.00 sec) mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )); +------+ | ref0 | +------+ | NULL | | NULL | | NULL | +------+ 3 rows in set (0.00 sec) mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t0.c0); Empty set (0.00 sec) I think `SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )) OR (t0.c0);` should contains 3 tuples as the sum of `SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t0.c0);` and `SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 ))`, but it dose not. How to repeat: Run the above sql.