Bug #121826 Prepared statement returns wrong result with ? in nested CTE read twice

Submitted: 18 Nov 13:47 Modified: 20 Nov 5:54
Reporter: Roy Razon Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.46, 8.4.13, 9.7.4, 26.10.0 OS:Oracle Linux (Oracle Linux Server 9.8)
Assigned to: CPU Architecture:x86 (aarch64 and x86_64)
Tags: cte, nested CTE, Parameter, placeholder, prepared statement, with, wrong result

[18 Nov 13:47] Roy Razon
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.
[20 Nov 5:54] Chaithra Marsur Gopala Reddy
Hi Roy Razon,

Thank you for the test case. Verified as described.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.