Bug #121673 ERROR 1030 / Wrong index given to function when reusing an EXCEPT or INTERSECT CTE
Description:
MySQL Community Server 9.7.1 rejects a SELECT that references an EXCEPT CTE from both the outer query and a correlated scalar subquery:
```text
ERROR 1030 (HY000): Got error 124 - 'Wrong index given to function' from storage engine
```
The reduced reproduction uses two single-column INT tables and one stored row. The right operand of EXCEPT is empty. The CTE therefore contains exactly one value, 1, and the query should return `(1, 1)`.
The error also occurs when the same CTE is referenced through a self-join or EXISTS, and with an INTERSECT CTE. Disabling the session optimizer switch `hash_set_operations` makes the main reproduction return the expected result. These observations point to reuse of a hash-based set-operation result; no source debugging has established the internal cause.
## Expected result
One row:
```text
a | matched
1 | 1
```
The EXCEPT right operand has no rows, so the CTE is `{1}`. The scalar subquery finds that same value and returns 1.
## Actual result
```text
ERROR 1030 (HY000): Got error 124 - 'Wrong index given to function' from storage engine
```
The reduced query failed in three consecutive executions. The original discovery query also failed in three consecutive executions in a separately rebuilt test database.
## Control: disable hash set operations
In the same connection and with the same data:
```sql
SET SESSION optimizer_switch = 'hash_set_operations=off';
WITH c AS (
SELECT a FROM input_a
EXCEPT
SELECT a FROM input_b
)
SELECT c.a,
(SELECT d.a FROM c AS d WHERE d.a = c.a LIMIT 1) AS matched
FROM c;
-- Returns (1, 1).
```
This session-level control also makes the original discovery query succeed. It is an investigation control, not a claim that disabling this optimization is desirable for general workloads.
How to repeat:
CREATE TABLE input_a (a INT);
INSERT INTO input_a VALUES (1);
CREATE TABLE input_b (a INT);
WITH c AS (
SELECT a FROM input_a
EXCEPT
SELECT a FROM input_b
)
SELECT c.a,
(SELECT d.a FROM c AS d WHERE d.a = c.a LIMIT 1) AS matched
FROM c;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.047 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.
Description: MySQL Community Server 9.7.1 rejects a SELECT that references an EXCEPT CTE from both the outer query and a correlated scalar subquery: ```text ERROR 1030 (HY000): Got error 124 - 'Wrong index given to function' from storage engine ``` The reduced reproduction uses two single-column INT tables and one stored row. The right operand of EXCEPT is empty. The CTE therefore contains exactly one value, 1, and the query should return `(1, 1)`. The error also occurs when the same CTE is referenced through a self-join or EXISTS, and with an INTERSECT CTE. Disabling the session optimizer switch `hash_set_operations` makes the main reproduction return the expected result. These observations point to reuse of a hash-based set-operation result; no source debugging has established the internal cause. ## Expected result One row: ```text a | matched 1 | 1 ``` The EXCEPT right operand has no rows, so the CTE is `{1}`. The scalar subquery finds that same value and returns 1. ## Actual result ```text ERROR 1030 (HY000): Got error 124 - 'Wrong index given to function' from storage engine ``` The reduced query failed in three consecutive executions. The original discovery query also failed in three consecutive executions in a separately rebuilt test database. ## Control: disable hash set operations In the same connection and with the same data: ```sql SET SESSION optimizer_switch = 'hash_set_operations=off'; WITH c AS ( SELECT a FROM input_a EXCEPT SELECT a FROM input_b ) SELECT c.a, (SELECT d.a FROM c AS d WHERE d.a = c.a LIMIT 1) AS matched FROM c; -- Returns (1, 1). ``` This session-level control also makes the original discovery query succeed. It is an investigation control, not a claim that disabling this optimization is desirable for general workloads. How to repeat: CREATE TABLE input_a (a INT); INSERT INTO input_a VALUES (1); CREATE TABLE input_b (a INT); WITH c AS ( SELECT a FROM input_a EXCEPT SELECT a FROM input_b ) SELECT c.a, (SELECT d.a FROM c AS d WHERE d.a = c.a LIMIT 1) AS matched FROM c;