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:	3 Apr 2024 10:06
Reporter:	liu hickey (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0	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.

Thank you Mr. Peng,

This concludes that this bug is already fixed.

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.

Hi,

This is still not a bug.

That is because you have forced the smallest intrinsic temporary table allowed in 8.0. These values can not be changed in the stable release, so it will be changed in 8.4.

Not a bug.