Description:
A prepared statement returns a wrong result when a ? parameter is used
inside a WITH clause that is nested in a CTE definition, the nested CTE
is referenced twice in that definition, and the outer CTE is referenced
twice in the outer query. The same statement with a literal in place of
the parameter returns the correct result. No error or warning is raised.
In the test case below, both scalar subqueries read the same CTE cm, so
both should return 1. The second one returns NULL:
expected: x=1, y=1
actual: x=1, y=NULL
Joining the CTE to itself (SELECT * FROM cm c1, cm c2) returns an empty
set instead of one row.
Reproduces on:
8.0.46, 8.4.8, 8.4.13, 9.7.4, 26.10.0
(official mysql Docker images, Linux aarch64; 8.4.13 also confirmed
on the linux/amd64 image, x86_64)
Default configuration, sql_mode default, no my.cnf changes.
It reproduces with both server-side PREPARE/EXECUTE and the binary
protocol (mysql-connector-python 26.10.0, cursor(prepared=True)).
It reproduces on the first EXECUTE and on every later EXECUTE.
Index, primary key and ANALYZE TABLE are not needed; one table with one
INT column and one row is enough.
Variants that return the correct result:
- the literal 1 instead of ?;
- the same statement executed directly (not prepared) with a user
variable @p instead of ?, or prepared with @p in the statement text;
- SET optimizer_switch='derived_merge=off';
- /*+ SET_VAR(optimizer_switch="derived_merge=off") */ in the
outermost SELECT;
- /*+ NO_MERGE(cm) */ in both scalar subqueries (in only one of them
is not enough);
- /*+ NO_MERGE(z1) NO_MERGE(z2) */ in the body of cm (either one
alone is not enough);
- moving z out of cm into the top-level WITH list
(WITH z AS (...), cm AS (...) SELECT ...);
- z referenced once in cm, or cm referenced once in the outer query;
- the condition a >= ? or a BETWEEN ? AND 1 instead of a = ?.
Variants that still return the wrong result:
- a = ? written as ? = a, a <=> ?, a IN (?), a <= ?, a = ? + 0,
a = CAST(? AS SIGNED);
- each of these optimizer_switch flags changed from its default:
derived_condition_pushdown=off, semijoin=off, materialization=off,
firstmatch=off, duplicateweedout=off, loosescan=off, hash_join=off,
condition_fanout_filter=off, subquery_to_derived=on,
block_nested_loop=off, subquery_materialization_cost_based=off;
- /*+ NO_MERGE(cm) */ or /*+ MERGE(cm) */ in the outermost SELECT
(cm is not referenced in that query block; NO_MERGE gives warning
3128 "Unresolved name `cm`@`select#1` for NO_MERGE hint").
derived_merge=off is not a complete workaround. On 8.4.8 it fixes the
test case below, but larger variants of the same shape still return
wrong results with it: cm read by two IN (SELECT ...) predicates, and
cm joined to itself in a query that also filters on it. Setting
derived_merge=off together with semijoin=off returned the correct
result for every variant we tested.
EXPLAIN FORMAT=TREE of the prepared statement (PREPARE of the EXPLAIN
text, EXECUTE ... USING @p) on 8.4.13. Select #2 and select #6 are the
two scalar subqueries. In select #6 the top filter is shown with ?,
while every other filter shows 1:
-> Rows fetched before execution (cost=0..0 rows=1)
-> Select #2 (subquery in projection; run only once)
-> Filter: (t1.a = 1) (cost=0.7 rows=1)
-> Inner hash join (no condition) (cost=0.7 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.a = 1) (cost=0.35 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
-> Select #6 (subquery in projection; run only once)
-> Filter: (t1.a = ?) (cost=0.7 rows=1)
-> Inner hash join (no condition) (cost=0.7 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.a = 1) (cost=0.35 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
EXPLAIN ANALYZE of the prepared statement shows that filter receiving
one row from the join and passing none:
-> Filter: (t1.a = ?) (cost=0.7 rows=1) (actual time=0.0106..0.0106 rows=0 loops=1)
-> Inner hash join (no condition) (cost=0.7 rows=1) (actual time=... rows=1 loops=1)
For the statement with the literal, both subqueries have the same plan,
with Filter: (t1.a = 1) on each table scan.
The optimizer trace of the prepared execution matches this: the WHERE
condition of select #2 is "((`t1`.`a` = 1) and (`t1`.`a` = 1))", and
the WHERE condition of select #6 is "((`t1`.`a` = ?) and (`t1`.`a` = 1))".
The EXPLAIN plans are the same on 26.10.0.
How to repeat:
-- Paste into the mysql client.
SELECT VERSION();
DROP DATABASE IF EXISTS bugtest;
CREATE DATABASE bugtest;
USE bugtest;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
SET @p = 1;
-- 1. Prepared statement with a ? parameter: wrong result
PREPARE s1 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = ?) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y';
EXECUTE s1 USING @p;
-- 2. Same statement with the literal 1 instead of ?: correct result
PREPARE s2 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = 1) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y';
EXECUTE s2;
-- 3. Same statement run directly with a user variable: correct result
WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = @p) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y;
-- 4. Statement 1 with derived_merge=off: correct result
SET optimizer_switch = 'derived_merge=off';
PREPARE s3 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = ?) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y';
EXECUTE s3 USING @p;
SET optimizer_switch = 'derived_merge=on';
-- 5. Joining the outer CTE to itself: empty result
PREPARE s4 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = ?) SELECT z1.a FROM z z1, z z2) SELECT * FROM cm c1, cm c2';
EXECUTE s4 USING @p;
DROP DATABASE bugtest;
Actual output (8.4.13; identical on 8.0.46, 8.4.8, 9.7.4, 26.10.0):
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.4.13 |
+-----------+
mysql> EXECUTE s1 USING @p;
+------+------+
| x | y |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.01 sec)
mysql> EXECUTE s2;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = @p) ...
+------+------+
| x | y |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> EXECUTE s3 USING @p; -- derived_merge=off
+------+------+
| x | y |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> EXECUTE s4 USING @p;
Empty set (0.00 sec)
Expected output:
EXECUTE s1 USING @p returns x=1, y=1, the same as statements 2, 3 and 4.
EXECUTE s4 USING @p returns one row (1, 1).
Suggested fix:
No fix is proposed. Observed workarounds, each of which returned the
correct result for this test case on all tested versions:
- SET optimizer_switch='derived_merge=off', or the hint
/*+ SET_VAR(optimizer_switch="derived_merge=off") */ in the
outermost SELECT (for the IN and self-join variants described
above, only together with semijoin=off);
- /*+ NO_MERGE(cm) */ in every query block that reads cm, or
/*+ NO_MERGE(z1) NO_MERGE(z2) */ in the body of cm;
- defining the nested CTE (z) in the top-level WITH list instead of
inside cm.
Description: A prepared statement returns a wrong result when a ? parameter is used inside a WITH clause that is nested in a CTE definition, the nested CTE is referenced twice in that definition, and the outer CTE is referenced twice in the outer query. The same statement with a literal in place of the parameter returns the correct result. No error or warning is raised. In the test case below, both scalar subqueries read the same CTE cm, so both should return 1. The second one returns NULL: expected: x=1, y=1 actual: x=1, y=NULL Joining the CTE to itself (SELECT * FROM cm c1, cm c2) returns an empty set instead of one row. Reproduces on: 8.0.46, 8.4.8, 8.4.13, 9.7.4, 26.10.0 (official mysql Docker images, Linux aarch64; 8.4.13 also confirmed on the linux/amd64 image, x86_64) Default configuration, sql_mode default, no my.cnf changes. It reproduces with both server-side PREPARE/EXECUTE and the binary protocol (mysql-connector-python 26.10.0, cursor(prepared=True)). It reproduces on the first EXECUTE and on every later EXECUTE. Index, primary key and ANALYZE TABLE are not needed; one table with one INT column and one row is enough. Variants that return the correct result: - the literal 1 instead of ?; - the same statement executed directly (not prepared) with a user variable @p instead of ?, or prepared with @p in the statement text; - SET optimizer_switch='derived_merge=off'; - /*+ SET_VAR(optimizer_switch="derived_merge=off") */ in the outermost SELECT; - /*+ NO_MERGE(cm) */ in both scalar subqueries (in only one of them is not enough); - /*+ NO_MERGE(z1) NO_MERGE(z2) */ in the body of cm (either one alone is not enough); - moving z out of cm into the top-level WITH list (WITH z AS (...), cm AS (...) SELECT ...); - z referenced once in cm, or cm referenced once in the outer query; - the condition a >= ? or a BETWEEN ? AND 1 instead of a = ?. Variants that still return the wrong result: - a = ? written as ? = a, a <=> ?, a IN (?), a <= ?, a = ? + 0, a = CAST(? AS SIGNED); - each of these optimizer_switch flags changed from its default: derived_condition_pushdown=off, semijoin=off, materialization=off, firstmatch=off, duplicateweedout=off, loosescan=off, hash_join=off, condition_fanout_filter=off, subquery_to_derived=on, block_nested_loop=off, subquery_materialization_cost_based=off; - /*+ NO_MERGE(cm) */ or /*+ MERGE(cm) */ in the outermost SELECT (cm is not referenced in that query block; NO_MERGE gives warning 3128 "Unresolved name `cm`@`select#1` for NO_MERGE hint"). derived_merge=off is not a complete workaround. On 8.4.8 it fixes the test case below, but larger variants of the same shape still return wrong results with it: cm read by two IN (SELECT ...) predicates, and cm joined to itself in a query that also filters on it. Setting derived_merge=off together with semijoin=off returned the correct result for every variant we tested. EXPLAIN FORMAT=TREE of the prepared statement (PREPARE of the EXPLAIN text, EXECUTE ... USING @p) on 8.4.13. Select #2 and select #6 are the two scalar subqueries. In select #6 the top filter is shown with ?, while every other filter shows 1: -> Rows fetched before execution (cost=0..0 rows=1) -> Select #2 (subquery in projection; run only once) -> Filter: (t1.a = 1) (cost=0.7 rows=1) -> Inner hash join (no condition) (cost=0.7 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Hash -> Filter: (t1.a = 1) (cost=0.35 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Select #6 (subquery in projection; run only once) -> Filter: (t1.a = ?) (cost=0.7 rows=1) -> Inner hash join (no condition) (cost=0.7 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Hash -> Filter: (t1.a = 1) (cost=0.35 rows=1) -> Table scan on t1 (cost=0.35 rows=1) EXPLAIN ANALYZE of the prepared statement shows that filter receiving one row from the join and passing none: -> Filter: (t1.a = ?) (cost=0.7 rows=1) (actual time=0.0106..0.0106 rows=0 loops=1) -> Inner hash join (no condition) (cost=0.7 rows=1) (actual time=... rows=1 loops=1) For the statement with the literal, both subqueries have the same plan, with Filter: (t1.a = 1) on each table scan. The optimizer trace of the prepared execution matches this: the WHERE condition of select #2 is "((`t1`.`a` = 1) and (`t1`.`a` = 1))", and the WHERE condition of select #6 is "((`t1`.`a` = ?) and (`t1`.`a` = 1))". The EXPLAIN plans are the same on 26.10.0. How to repeat: -- Paste into the mysql client. SELECT VERSION(); DROP DATABASE IF EXISTS bugtest; CREATE DATABASE bugtest; USE bugtest; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); SET @p = 1; -- 1. Prepared statement with a ? parameter: wrong result PREPARE s1 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = ?) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y'; EXECUTE s1 USING @p; -- 2. Same statement with the literal 1 instead of ?: correct result PREPARE s2 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = 1) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y'; EXECUTE s2; -- 3. Same statement run directly with a user variable: correct result WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = @p) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y; -- 4. Statement 1 with derived_merge=off: correct result SET optimizer_switch = 'derived_merge=off'; PREPARE s3 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = ?) SELECT z1.a FROM z z1, z z2) SELECT (SELECT a FROM cm) AS x, (SELECT a FROM cm) AS y'; EXECUTE s3 USING @p; SET optimizer_switch = 'derived_merge=on'; -- 5. Joining the outer CTE to itself: empty result PREPARE s4 FROM 'WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = ?) SELECT z1.a FROM z z1, z z2) SELECT * FROM cm c1, cm c2'; EXECUTE s4 USING @p; DROP DATABASE bugtest; Actual output (8.4.13; identical on 8.0.46, 8.4.8, 9.7.4, 26.10.0): mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.4.13 | +-----------+ mysql> EXECUTE s1 USING @p; +------+------+ | x | y | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.01 sec) mysql> EXECUTE s2; +------+------+ | x | y | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) mysql> WITH cm AS (WITH z AS (SELECT a FROM t1 WHERE a = @p) ... +------+------+ | x | y | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) mysql> EXECUTE s3 USING @p; -- derived_merge=off +------+------+ | x | y | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) mysql> EXECUTE s4 USING @p; Empty set (0.00 sec) Expected output: EXECUTE s1 USING @p returns x=1, y=1, the same as statements 2, 3 and 4. EXECUTE s4 USING @p returns one row (1, 1). Suggested fix: No fix is proposed. Observed workarounds, each of which returned the correct result for this test case on all tested versions: - SET optimizer_switch='derived_merge=off', or the hint /*+ SET_VAR(optimizer_switch="derived_merge=off") */ in the outermost SELECT (for the IN and self-join variants described above, only together with semijoin=off); - /*+ NO_MERGE(cm) */ in every query block that reads cm, or /*+ NO_MERGE(z1) NO_MERGE(z2) */ in the body of cm; - defining the nested CTE (z) in the top-level WITH list instead of inside cm.