Description:
When the session variable `sql_buffer_result` is `ON` (equivalently, every SELECT behaves as
`SELECT SQL_BUFFER_RESULT ...`), an `IN` or quantified-comparison (`= ANY`, `<> ANY`, `< ALL`,
`<= ALL`, `> ALL`, `>= ALL`, `= SOME`, `<> SOME`) subquery predicate placed in the **projection
list** is wrongly evaluated to **NULL** for every row — including rows where the predicate is
plainly TRUE. The same predicate in the `WHERE` clause of the same query is evaluated correctly.
Impact boundary (all verified on 9.7.2):
| Shape (with `sql_buffer_result=ON`) | Result | Correct? |
|---|---|---|
| `WHERE c0 IN (SELECT c0 FROM t0)` | {1, 2} | yes |
| Projection `(c0 IN (SELECT c0 FROM t0))` | NULL, NULL, NULL | **NO** (expected 1, 1, NULL) |
| Projection `(c0 <> ANY (SELECT c0 FROM t0))` | NULL, NULL, NULL | **NO** (expected 1, 1, NULL) |
| Projection `(c0 > ALL (SELECT c0 FROM t0 WHERE 0))` (empty set) | 1, 1, 1 | yes |
| Projection `((c0 IN (...)) IS TRUE)` | 1, 1, 0 | yes |
| Projection `(EXISTS (SELECT 1 FROM t0 s WHERE s.c0 = t0.c0))` | 1, 1, 0 | yes |
| Projection `((SELECT MAX(c0) FROM t0) > 0)` (scalar subquery) | 1, 1, 1 | yes |
| Projection `(c0 > 0)` (plain predicate) | 1, 1, NULL | yes |
So the defect is specific to the **bare boolean evaluation of IN/quantified subquery predicates
in the SELECT list** when results are buffered into a temporary table. `EXISTS`, scalar
subqueries, postfix `IS TRUE`, constant-folded empty-set `ALL`, and plain predicates are all
unaffected. `NOT IN` is affected the same way as `IN`.
This is not a duplicate of any entry in the project's tracked known-bug set
(MySQLBugs: 95894, 99127, 99135, 99181, 99183, 111471, 112242, 112243, 112264, 114533,
114534 — all unsigned/BETWEEN/collation families, none involving `sql_buffer_result` or
subquery projection), and no matching open report was found on bugs.mysql.com for
"sql_buffer_result IN subquery projection NULL".
How to repeat:
```sql
DROP DATABASE IF EXISTS repro_buf_in;
CREATE DATABASE repro_buf_in;
USE repro_buf_in;
CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES (1),(2),(NULL);
SET SESSION sql_buffer_result = ON;
-- (1) The predicate in the projection list: all rows become NULL <-- WRONG
SELECT c0, (c0 IN (SELECT c0 FROM t0)) AS in_subq FROM t0;
-- actual: 1 NULL / 2 NULL / NULL NULL
-- expected: 1 1 / 2 1 / NULL NULL
-- (2) The same predicate in WHERE: correct
SELECT c0 FROM t0 WHERE c0 IN (SELECT c0 FROM t0);
-- returns {1, 2} <-- correct
-- (3) DQR rewritten form (predicate relocated into a derived-table projection): drops rows
SELECT ref0 FROM (
SELECT c0 AS ref0, (c0 IN (SELECT c0 FROM t0)) AS ref1 FROM t0
) s WHERE ref1;
-- actual: empty set <-- WRONG (expected {1, 2}, logically equivalent to query (2))
SET SESSION sql_buffer_result = OFF;
-- (4) Control: with the flag off, the projection is correct
SELECT c0, (c0 IN (SELECT c0 FROM t0)) AS in_subq FROM t0;
-- returns 1 1 / 2 1 / NULL NULL <-- correct
```
Quantified forms reproduce identically, e.g. `SELECT c0, (c0 <> ANY (SELECT c0 FROM t0)) FROM t0;` returns all NULL with the flag on (expected 1, 1, NULL).
Queries (1), (2) and (3) are semantically equivalent and must agree: the IN predicate is TRUE for rows 1 and 2 (each value is present in the table) and NULL for the NULL row, regardless of whether the predicate is evaluated in the WHERE clause or in the projection list, and independently of `sql_buffer_result` (which must only affect how results are buffered, never query semantics).
However, with `sql_buffer_result=ON`, the projection form evaluates the IN/quantified predicate to NULL for every row, so any client reading the computed column — or any derived table filtering on it — observes wrong results (query (3) wrongly returns the empty set).
Description: When the session variable `sql_buffer_result` is `ON` (equivalently, every SELECT behaves as `SELECT SQL_BUFFER_RESULT ...`), an `IN` or quantified-comparison (`= ANY`, `<> ANY`, `< ALL`, `<= ALL`, `> ALL`, `>= ALL`, `= SOME`, `<> SOME`) subquery predicate placed in the **projection list** is wrongly evaluated to **NULL** for every row — including rows where the predicate is plainly TRUE. The same predicate in the `WHERE` clause of the same query is evaluated correctly. Impact boundary (all verified on 9.7.2): | Shape (with `sql_buffer_result=ON`) | Result | Correct? | |---|---|---| | `WHERE c0 IN (SELECT c0 FROM t0)` | {1, 2} | yes | | Projection `(c0 IN (SELECT c0 FROM t0))` | NULL, NULL, NULL | **NO** (expected 1, 1, NULL) | | Projection `(c0 <> ANY (SELECT c0 FROM t0))` | NULL, NULL, NULL | **NO** (expected 1, 1, NULL) | | Projection `(c0 > ALL (SELECT c0 FROM t0 WHERE 0))` (empty set) | 1, 1, 1 | yes | | Projection `((c0 IN (...)) IS TRUE)` | 1, 1, 0 | yes | | Projection `(EXISTS (SELECT 1 FROM t0 s WHERE s.c0 = t0.c0))` | 1, 1, 0 | yes | | Projection `((SELECT MAX(c0) FROM t0) > 0)` (scalar subquery) | 1, 1, 1 | yes | | Projection `(c0 > 0)` (plain predicate) | 1, 1, NULL | yes | So the defect is specific to the **bare boolean evaluation of IN/quantified subquery predicates in the SELECT list** when results are buffered into a temporary table. `EXISTS`, scalar subqueries, postfix `IS TRUE`, constant-folded empty-set `ALL`, and plain predicates are all unaffected. `NOT IN` is affected the same way as `IN`. This is not a duplicate of any entry in the project's tracked known-bug set (MySQLBugs: 95894, 99127, 99135, 99181, 99183, 111471, 112242, 112243, 112264, 114533, 114534 — all unsigned/BETWEEN/collation families, none involving `sql_buffer_result` or subquery projection), and no matching open report was found on bugs.mysql.com for "sql_buffer_result IN subquery projection NULL". How to repeat: ```sql DROP DATABASE IF EXISTS repro_buf_in; CREATE DATABASE repro_buf_in; USE repro_buf_in; CREATE TABLE t0(c0 INT); INSERT INTO t0 VALUES (1),(2),(NULL); SET SESSION sql_buffer_result = ON; -- (1) The predicate in the projection list: all rows become NULL <-- WRONG SELECT c0, (c0 IN (SELECT c0 FROM t0)) AS in_subq FROM t0; -- actual: 1 NULL / 2 NULL / NULL NULL -- expected: 1 1 / 2 1 / NULL NULL -- (2) The same predicate in WHERE: correct SELECT c0 FROM t0 WHERE c0 IN (SELECT c0 FROM t0); -- returns {1, 2} <-- correct -- (3) DQR rewritten form (predicate relocated into a derived-table projection): drops rows SELECT ref0 FROM ( SELECT c0 AS ref0, (c0 IN (SELECT c0 FROM t0)) AS ref1 FROM t0 ) s WHERE ref1; -- actual: empty set <-- WRONG (expected {1, 2}, logically equivalent to query (2)) SET SESSION sql_buffer_result = OFF; -- (4) Control: with the flag off, the projection is correct SELECT c0, (c0 IN (SELECT c0 FROM t0)) AS in_subq FROM t0; -- returns 1 1 / 2 1 / NULL NULL <-- correct ``` Quantified forms reproduce identically, e.g. `SELECT c0, (c0 <> ANY (SELECT c0 FROM t0)) FROM t0;` returns all NULL with the flag on (expected 1, 1, NULL). Queries (1), (2) and (3) are semantically equivalent and must agree: the IN predicate is TRUE for rows 1 and 2 (each value is present in the table) and NULL for the NULL row, regardless of whether the predicate is evaluated in the WHERE clause or in the projection list, and independently of `sql_buffer_result` (which must only affect how results are buffered, never query semantics). However, with `sql_buffer_result=ON`, the projection form evaluates the IN/quantified predicate to NULL for every row, so any client reading the computed column — or any derived table filtering on it — observes wrong results (query (3) wrongly returns the empty set).