Description:
the results of the two queries below are inconsistent.
mysql> SET SESSION sql_buffer_result = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SET optimizer_switch = 'materialization=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2)));
+------+
| c5 |
+------+
| r |
+------+
1 row in set (0.00 sec)
mysql> SET SESSION sql_buffer_result = Off;
Query OK, 0 rows affected (0.00 sec)
mysql> SET optimizer_switch = 'materialization=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2)));
Empty set (0.01 sec)
How to repeat:
CREATE TABLE `t0` (
`c1` float DEFAULT NULL,
`c5` varchar(500) DEFAULT NULL,
`c9` decimal(10,0) unsigned zerofill DEFAULT NULL
);
INSERT INTO `t0` VALUES(0.286385,'r',0);
CREATE TABLE `t2` (
`c2` float
);
INSERT INTO `t2` VALUES (0.615793);
create table tmp_t0(c5 varchar(500));
insert into tmp_t0 values(NULL), ('0.1002505534510798');
SET SESSION sql_buffer_result = ON;
SET optimizer_switch = 'materialization=off';
SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2)));
SET SESSION sql_buffer_result = Off;
SET optimizer_switch = 'materialization=on';
SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2)));
Description: the results of the two queries below are inconsistent. mysql> SET SESSION sql_buffer_result = ON; Query OK, 0 rows affected (0.00 sec) mysql> SET optimizer_switch = 'materialization=off'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2))); +------+ | c5 | +------+ | r | +------+ 1 row in set (0.00 sec) mysql> SET SESSION sql_buffer_result = Off; Query OK, 0 rows affected (0.00 sec) mysql> SET optimizer_switch = 'materialization=on'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2))); Empty set (0.01 sec) How to repeat: CREATE TABLE `t0` ( `c1` float DEFAULT NULL, `c5` varchar(500) DEFAULT NULL, `c9` decimal(10,0) unsigned zerofill DEFAULT NULL ); INSERT INTO `t0` VALUES(0.286385,'r',0); CREATE TABLE `t2` ( `c2` float ); INSERT INTO `t2` VALUES (0.615793); create table tmp_t0(c5 varchar(500)); insert into tmp_t0 values(NULL), ('0.1002505534510798'); SET SESSION sql_buffer_result = ON; SET optimizer_switch = 'materialization=off'; SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2))); SET SESSION sql_buffer_result = Off; SET optimizer_switch = 'materialization=on'; SELECT t0.c5 FROM t0, t2 WHERE (((t0.c5) IN (select c5 from tmp_t0)) IN ((48085430) IN (t0.c1))) IN (((t0.c9) IN (t2.c2)));