Bug #120185 Assertion Failure in add_key_fields with Nested CTE
Submitted: 31 Mar 8:58 Modified: 6 Apr 4:47
Reporter: Chunling Qin Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.32-debug OS:Any
Assigned to: CPU Architecture:Any

[31 Mar 8:58] Chunling Qin 
Description:
When executing a query with nested CTEs where:
1. Both outer and inner CTEs have the same name (`cte1`)
2. The inner CTE contains a LEFT OUTER JOIN with a scalar subquery in the ON condition
3. The WHERE clause contains an IN predicate with column references combined with an OR condition

The server crashes with assertion failure in `add_key_fields()` function at `sql/sql_optimizer.cc:7267`:
```
assert(cond->is_bool_func())
```

## Expected Behavior
The query should either execute successfully or return a proper error message if there is a semantic issue with the query.

## Actual Behavior
Server crashes with assertion failure.

## Error Log
```
mysqld: /data/mysql-server/sql/sql_optimizer.cc:7267: bool add_key_fields(THD *, JOIN *, Key_field **, uint *, Item *, table_map, SARGABLE_PARAM **): Assertion `cond->is_bool_func()' failed.
2026-03-31T08:31:34Z UTC - mysqld got signal 6;

stack_bottom = 7f744c195ad8 thread_stack 0x100000
/data/mysql-server/build_cov/runtime_output_directory/mysqld(my_print_stacktrace+0x58)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(print_fatal_signal+0x2de)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(handle_fatal_signal+0xa2)
/lib64/libc.so.6(+0x3ebb0)
/lib64/libc.so.6(+0x91294)
/lib64/libc.so.6(raise+0x16)
/lib64/libc.so.6(abort+0xd7)
/lib64/libc.so.6(+0x2679b)
/lib64/libc.so.6(+0x371a6)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(add_key_fields+0x8a)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(add_key_fields+0x152)  # recursive call
/data/mysql-server/build_cov/runtime_output_directory/mysqld(+0x7835813)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(JOIN::make_join_plan()+0x34d)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(JOIN::optimize(bool)+0x24c2)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(Query_block::optimize(THD*, bool)+0x1d5)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x32b)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(TABLE_LIST::optimize_derived(THD*)+0x2c9)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(JOIN::optimize(bool)+0xa59)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(Query_block::optimize(THD*, bool)+0x1d5)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x32b)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x50)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(Sql_cmd_dml::execute(THD*)+0xa93)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x9cb4)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(dispatch_sql_command(THD*, Parser_state*)+0xd79)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x241b)
/data/mysql-server/build_cov/runtime_output_directory/mysqld(do_command(THD*)+0x7a0)
```

## Root Cause Analysis

The crash occurs in the query optimizer during the `make_join_plan()` phase. The `add_key_fields()` function expects its `cond` parameter to be a boolean function (condition), but receives a non-boolean expression.

How to repeat:
USE test;

CREATE TABLE mysql_1 (
  col_int int,
  col_varchar varchar(2000),
  col_date date,
  col_timestamp timestamp,
  col_numeric numeric,
  KEY k2 (col_int,col_date),
  KEY k1 (col_numeric)
);

CREATE TABLE mysql_2 (
  col_int int,
  col_varchar varchar(2000),
  col_date date,
  col_timestamp timestamp,
  col_numeric numeric,
  KEY k2 (col_int,col_date),
  KEY k1 (col_numeric)
);

CREATE TABLE mysql_3 (
  col_int int,
  col_varchar varchar(2000),
  col_date date,
  col_timestamp timestamp,
  col_numeric numeric,
  KEY k2 (col_int,col_date),
  KEY k1 (col_numeric)
);

CREATE TABLE mysql_4 (
  col_int int,
  col_varchar varchar(2000),
  col_date date,
  col_timestamp timestamp,
  col_numeric numeric,
  KEY k2 (col_int,col_date),
  KEY k1 (col_numeric)
);

CREATE TABLE mysql_5 (
  col_int int,
  col_varchar varchar(2000),
  col_date date,
  col_timestamp timestamp,
  col_numeric numeric,
  KEY k2 (col_int,col_date),
  KEY k1 (col_numeric)
);

CREATE TABLE mysql_6 (
  col_int int,
  col_varchar varchar(2000),
  col_date date,
  col_timestamp timestamp,
  col_numeric numeric,
  KEY k2 (col_int,col_date),
  KEY k1 (col_numeric)
);

-- Insert sample data
INSERT INTO mysql_1 VALUES (5, NULL, '1979-11-25', '2014-01-16', 0), (0, 'tgenv', '1972-03-09', '1972-01-01 08:35:24', NULL);
INSERT INTO mysql_2 VALUES (1, NULL, '1973-12-22', '2012-02-04', NULL), (4, 'e', '1995-07-09', '2029-03-02', 5);
INSERT INTO mysql_3 VALUES (NULL, 'm', '2011-05-03', '1994-07-28', 0), (0, 'english', '1992-05-15', '2027-03-27', 1);
INSERT INTO mysql_4 VALUES (4, 'x', '2008-06-19', '1998-03-25', 8), (NULL, 'h', '1975-04-10', '1971-08-23', 1);
INSERT INTO mysql_5 VALUES (5, 'english', '1981-09-16', '1975-09-20', 8), (NULL, 'ldi', '1979-07-12', '2016-11-12', NULL);
INSERT INTO mysql_6 VALUES (5, 'p', '2005-11-01', '1999-05-28', 0), (NULL, 'english', '2013-07-12', '2009-09-28', 4);

-- Create temporary tables
CREATE TABLE tmp2 AS SELECT * FROM mysql_3;
CREATE TABLE tmp7 AS SELECT * FROM mysql_2;
```

### 2. Execute the crash-inducing query:
```sql
WITH cte1 AS (
    WITH cte1 AS (
        SELECT a2.col_int, a2.col_varchar, a1.col_date, a2.col_timestamp, a1.col_numeric
        FROM tmp2 a1
        LEFT OUTER JOIN mysql_5 a2 ON (
            a1.col_int = (
                SELECT MAX(col_int)
                FROM (
                    SELECT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
                    FROM mysql_6 a1
                    WHERE a1.col_date NOT BETWEEN '1995-05-23' AND '1995-05-23' + 1
                    GROUP BY a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
                    HAVING (a1.col_int NOT IN (2, 4, 1, 6, 9, 6)) OR (a1.col_int = 13314)
                ) mm
            )
        )
        WHERE a2.col_timestamp IN (a1.col_timestamp, a2.col_timestamp) OR a2.col_numeric IS NULL
    ),
    cte2 AS (
        SELECT DISTINCT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
        FROM tmp7 a1
        WHERE a1.col_int IS NOT NULL AND a1.col_varchar LIKE 'C%'
        GROUP BY a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
        HAVING a1.col_varchar LIKE 'C%'
    )
    SELECT * FROM cte1 GROUP BY col_varchar
),
cte2 AS (
    SELECT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
    FROM (
        SELECT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
        FROM mysql_4 a1
        WHERE (a1.col_int BETWEEN 5 AND 5 + 10 AND a1.col_date NOT BETWEEN '1995-05-23' AND '1995-05-23' + 1)
           OR (a1.col_varchar LIKE 'C%') AND a1.col_date IN ('1992-01-13', '2026-11-02', '2017-03-21', '1972-01-06', '2016-09-11', '1989-05-11')
        GROUP BY a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
        HAVING NOT (a1.col_date = a1.col_date)
        UNION
        SELECT DISTINCT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
        FROM mysql_1 a1
        WHERE NOT (a1.col_date > a1.col_date) AND a1.col_numeric IS NULL
    ) a1
    WHERE NOT (a1.col_numeric = '0.446533203125') AND a1.col_date IS NULL
)
SELECT t11.* FROM cte2 t11 ORDER BY 1 LIMIT 1 OFFSET 1 FOR SHARE;
[31 Mar 9:27] Chaithra Marsur Gopala Reddy 
Hi Chunling Qin,

We are unable to reproduce the problem on latest MySQL-8.0 release. You seem to be on a much older MySQL version. Could you please try on MySQL-8.0.45.
[31 Mar 12:43] Chunling Qin 
could not reproduce with 8.4.3.

    ->     FROM (
    ->         SELECT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
    ->         FROM mysql_4 a1
    ->         WHERE (a1.col_int BETWEEN 5 AND 5 + 10 AND a1.col_date NOT BETWEEN '1995-05-23' AND '1995-05-23' + 1)
    ->            OR (a1.col_varchar LIKE 'C%') AND a1.col_date IN ('1992-01-13', '2026-11-02', '2017-03-21', '1972-01-06', '2016-09-11', '1989-05-11')
    ->         GROUP BY a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
    ->         HAVING NOT (a1.col_date = a1.col_date)
    ->         UNION
    ->         SELECT DISTINCT a1.col_int, a1.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric
    ->         FROM mysql_1 a1
    ->         WHERE NOT (a1.col_date > a1.col_date) AND a1.col_numeric IS NULL
    ->     ) a1
    ->     WHERE NOT (a1.col_numeric = '0.446533203125') AND a1.col_date IS NULL
    -> )
    -> SELECT t11.* FROM cte2 t11 ORDER BY 1 LIMIT 1 OFFSET 1 FOR SHARE;
Empty set, 3 warnings (0.00 sec)

MySQL [fuzztest]> 
MySQL [fuzztest]> 
MySQL [fuzztest]> select version();
+-----------+
| version() |
+-----------+
| 8.4.3     |
+-----------+