MySQL Bugs: #112704: CTE query got internal table doesn't exist error with small tmp_table

Bug #112704	CTE query got internal table doesn't exist error with small tmp_table_size
Submitted:	12 Oct 2023 12:19	Modified:	4 Dec 4:20
Reporter:	liu hickey (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0, 8.4.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
With small tmp_table_size(4000), CTE query might got internal table not found error: 
```
ERROR 1146 (42S02): Table 'xxx/#sql2d28_b_2' doesn't exist
```

I verified with released branches and trunk:
- still fail in 8.0.34 (lasted release version)
- succeed in latest trunk (
ea1efa9822d     Bjorn Munch     Wed Jun 21 09:22:55 2023 +0200  Update License Book)

I tried to find some commit msg for such bugfix but failed. So I am not sure it will be fixed in next release.

How to repeat:
1. start mysqld with tmp_table_size=4000
2. load 1G tpcds
3. execute query1, internal tmptable doesn't exist!

```
mysql> with customer_total_return as (select sr_customer_sk as ctr_customer_sk,                                       sr_store_sk    as ctr_store_sk,
               sum(SR_FEE)    as ctr_total_return                                from store_returns,                                     date_dim
    where sr_returned_date_sk = d_date_sk                                  and d_year = 2000                                group by sr_customer_sk, sr_store_sk) select c_cu
stomer_id from customer_total_return ctr1,      store,      customer where ctr1.ctr_total_return >       (select avg(ctr_total_return) * 1.2 from customer_total_return ctr2
where ctr1.ctr_store_sk = ctr2.ctr_store_sk)   and s_store_sk = ctr1.ctr_store_sk   and s_state = 'TN'   and ctr1.ctr_customer_sk = c_customer_sk order by c_customer_id limi
t 100;
ERROR 1146 (42S02): Table './data/hickey/mysql/build/mysql-test/var/tmp/mysqld.1/#sql2d28_b_2' doesn't exist

```

Hi Mr. hickey,

Thank you very much for your bug report.

However, we need a fully repeatable test case.

Please send us a full table definition with sufficient number of rows, so that we could repeat it. Please, do not direct us to third-party tools, since we use only our own.

Your tmp_table_size is within the limits, so if we manage to reproduce the behaviour, it will, most likely, be a verified bug.

Can't repeat.

I got an easy repeatable case from main.table_value_constructor in mysql-test, that is:

1. set tmp_table_size with minimal value
mysql> set global tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

2. reconnect and execute cte query
mysql> WITH v AS (VALUES ROW(RAND(0)), ROW(RAND(1))) SELECT * FROM v AS v1 JOIN v AS v2;
ERROR 1146 (42S02): Table './data/hickey/mysql/build/mysql-test/var/tmp/mysqld.1/#sql6b5_9_0' doesn't exist

Hi Mr. hickey,

We used latest 8.0 and latest 8.1 and ran your test case.

We have set both global and session variable and then reconnected.

Then we ran the query and got this results:

WITH v AS (VALUES ROW(RAND(0)), ROW(RAND(1))) SELECT * FROM v AS v1 JOIN v AS v2;
+---------------------+---------------------+
| column_0            | column_0            |
+---------------------+---------------------+
| 0.40540353712197724 | 0.15522042769493574 |
| 0.15522042769493574 | 0.15522042769493574 |
| 0.40540353712197724 | 0.40540353712197724 |
| 0.15522042769493574 | 0.40540353712197724 |
+---------------------+---------------------+

Can't repeat.

I believe this bug has not been fixed in mysql-8.0.36, I tried the same steps as hickey:

mysql> set global tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

reconnect

mysql> WITH v AS (VALUES ROW(RAND(0)), ROW(RAND(1))) SELECT * FROM v AS v1 JOIN v AS v2;
ERROR 1146 (42S02): Table './data/mysql-server/bld-debug/mysql-test/var/tmp/mysqld.1/#sql7f7c_9_0' doesn't exist

After some debugging, I found that the problem occurs after the failure of creating a temporary table with temptable_hton in function create_tmp_table_with_fallback.

I also tried mysql-8.0.10 and it can not be repeated, I found this commit (https://github.com/mysql/mysql-server/commit/ad7ee45) works.

However, I don't think this commit addresses the actual problem, it merely prevents the failure of temporary table creation under certain situations.

Oh, sorry, not mysql-8.0.10:
I tried mysql-8.1.0 and it can not be repeated.

Hello, as mentioned earlier, the actual problem has not been fixed yet. The commit(https://github.com/mysql/mysql-server/commit/ad7ee45) only prevents some failures of temporary table creation. However, if the creation still fails, the error will still be reported . I have managed to repeat it in mysql-8.3.0 by creating a table with many columns(1000+). I will provide a test script later.

Previously uploaded files are only visible to developers

Attachment: test.sql (application/octet-stream, text), 31.40 KiB.

I believe the root cause is in sql/iterators/composite_iterators.cc in MaterializeIterator::Init().

When a CTE is referenced multiple times, follower clones that use use_shared_cte_materialization set table()->materialized = true and return
early without calling open_tmp_table(). This means only the leader clone increments tmp_open_count. When the leader closes via
close_tmp_table(), the count reaches 0 and ha_drop_table() deletes the file while followers still need it.

Fix: After setting materialized = true for shared CTE followers, call open_tmp_table() to increment tmp_open_count:

if (use_shared_cte_materialization) {
    // ... existing code ...
    table()->materialized = true;

    // Open shared temp file to increment tmp_open_count, preventing
    // premature file deletion when the leader clone closes.
    if (!table()->is_created() && open_tmp_table(table())) return true;
}

Note: We hit this issue with tmpdir on tmpfs ramdisk (150GB) and tmp_table_size=4G. Fast storage like ramdisk exposes this bug more frequently
because file deletion is instantaneous, whereas slower storage (SSD/HDD) may mask the race condition due to I/O latency.

Thank you, Yakir Gibraltar

Thank you,  repeated this on 8.4.7 using "test.sql" attachment.

    ->   FROM
    ->     t1
    ->     JOIN ctetest ON t1.id = ctetest.creator_id
    -> )
    -> SELECT
    ->   id
    -> FROM
    ->   ctetest
    -> WHERE
    ->   c1 = 1;
ERROR 1146 (42S02): Table '.c:\tmp\#sqlcb70_7_0' doesn't exist
mysql> select version();
+------------------+
| version()        |
+------------------+
| 8.4.7-commercial |
+------------------+
1 row in set (0.00 sec)