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:
None 
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 6:58] Jingqi Tian
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 |
+------------------------+
[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.