MySQL Bugs: #116741: Innodb tmp table causes incorrect query results

Bug #116741	Innodb tmp table causes incorrect query results
Submitted:	21 Nov 2024 6:58	Modified:	22 Nov 2024 1:05
Reporter:	Jingqi Tian (OCA)	Email Updates:
Status:	Verified	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

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 |
+------------------------+

Hello Jingqi Tian,

Thank you for the report and test case.

regards,
Umesh

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.

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.