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

• Submitted: 11 Oct 2:30
• Reporter: Alice Alice
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[11 Oct 2:30] Alice Alice

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