Bug #119538 different values set for sql_buffer_result and materialization lead to different query results.

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)));

Hi David silva,

Thank you for the test case. Verified as described.