Bug #112915 CTE double materialization issue

• Submitted: 1 Nov 2023 10:27
• Modified: 2 Nov 2023 8:08
• Reporter: Pedro Ferreira
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.1.0, 8.0.35
• OS: Ubuntu (22.04)
• CPU Architecture: x86 (x86_64)
• Tags: cte

[1 Nov 2023 10:27] Pedro Ferreira

Description:
I am not sure if this can be labeled as a bug or a missing feature, so please close if this is not the case.

Run these queries:
CREATE TEMPORARY TABLE t0 (c0 INT);
WITH x(x) AS (SELECT 1 FROM t0) SELECT 1 FROM x WHERE (SELECT 1 FROM x);

The CTE query will fail with the error:
ERROR 1137 (HY000): Can't reopen table: 't0'

This is happening because table t0 is being opened twice. However, I would expect the CTE to be materialized only once during the whole query, then the error shouldn't happen. Can this be improved?

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

[2 Nov 2023 7:11] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and test case.

regards,
Umesh

[2 Nov 2023 8:08] Roy Lyseng

Posted by developer:
 
This is not a bug. Check documentation in Error messages appendix of the user manual:

You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

  SELECT * FROM temp_table JOIN temp_table AS t2;

The statement produces this error:

  ERROR 1137: Can't reopen table: 'temp_table'

You can work around this issue if your query permits use of a common table expression (CTE) rather than a TEMPORARY table. For example, this fails with the Can't reopen table error:
To avoid the error, use a WITH clause that defines a CTE, rather than the TEMPORARY table: