Bug #116741 Innodb tmp table causes incorrect query results
| Submitted: | 21 Nov 2024 6:58 | Modified: | 13 Aug 16:15 |
|---|---|---|---|
| Reporter: | Jingqi Tian (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0, 8.0.40, 8.4.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[21 Nov 2024 7:53]
MySQL Verification Team
Hello Jingqi Tian, Thank you for the report and test case. regards, Umesh
[22 Nov 2024 1:04]
Jingqi Tian
Fix CONST access type can‘t read data from disk temporary table (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug#116741.patch (application/octet-stream, text), 3.11 KiB.
[22 Nov 2024 1:05]
Jingqi Tian
The cause of this bug is that after converting the In-memory temporary table to a disk temporary table, the TABLE is not set to not started, which causes CONST access error.
[28 May 1:13]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[13 Aug 16:15]
Edward Gilmore
Posted by developer:
Added the following note to the MySQL Server 8.4.11, 9.7.2, and 26.7.0 release notes:
Queries using CONST access on a materialized temporary table
could return incorrect results after an in-memory temporary
table was converted to an on-disk table.
As of this release, the temporary table's read state is reset
after conversion, ensuring the expected rows remain accessible.
Our thanks to Jingqi Tian for the contribution.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.057 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: The same SQL, when the materialized temporary table is an InnoDB table, the query results are incorrect. How to repeat: 1. Create table CREATE TABLE t1 ( id int PRIMARY KEY, col1 varchar(32), key index_col1(col1) ); CREATE TABLE t2 ( id int PRIMARY KEY, col1 varchar(32) ); 2. Create procedure CREATE PROCEDURE insert_data () BEGIN DECLARE num int; SET num = 1; WHILE num < 1000 DO INSERT INTO t1 VALUES (num, md5(num)); INSERT INTO t2 VALUES (num, md5(num)); SET num = num + 1; END WHILE; END; 3. Call procedure to insert data call insert_data(); 4. Execute Query set internal_tmp_mem_storage_engine = 'MEMORY'; set tmp_table_size = 1024000; select count(distinct d.col1) from (select t1.col1 from t1 union select t1.col1 from t1) d join t2 on d.col1 = t2.col1 where t2.id = 1; +------------------------+ | count(distinct d.col1) | +------------------------+ | 1 | +------------------------+ The result is correct. However, if the 'tmp_table_size' is 1024, the result will be incorrect. set tmp_table_size = 1024; select count(distinct d.col1) from (select t1.col1 from t1 union select t1.col1 from t1) d join t2 on d.col1 = t2.col1 where t2.id = 1; +------------------------+ | count(distinct d.col1) | +------------------------+ | 0 | +------------------------+