Bug #108315 Wrong result with union and small memory tmp table

• Submitted: 29 Aug 2022 8:27
• Modified: 29 Aug 2022 8:42
• Reporter: mengchu shi (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0, 8.0.30
• OS: Linux
• CPU Architecture: Any
• Tags: temp table, UNION, UNION ALL

[29 Aug 2022 8:27] mengchu shi

Description:
If a query with UNION uses memory tmp_table bigger than tmp_table_size, it may get a wrong result. It is worth mentioning:
1. using UNION ALL instead of UNION can get true result.
2. setting tmp_table_size to a properly big value can get true result.

How to repeat:
wrong_result_with_union_and_small_memory_tmp_table.test
```
--disable_query_log
--disable_result_log

# 1. prepare
drop table if exists t1;
create table t1(id varchar(50));

# 2. create PROCEDURE
delimiter $$;
drop PROCEDURE if exists bulk_insert1;
CREATE PROCEDURE bulk_insert1(IN count INTEGER)
BEGIN
      declare i int default 1;
    declare s1 varchar(50);
    declare s2 varchar(50);
    declare s3 varchar(50);
    declare s4 varchar(50);
    declare s5 varchar(50);
    declare str varchar(50);
    START TRANSACTION;
    while i<=count DO
        set s1 = substring(md5(rand()),1,10);
        set s2 = substring(md5(rand()),1,4);
        set s3 = substring(md5(rand()),1,4);
        set s4 = substring(md5(rand()),1,4);
        set s5 = substring(md5(rand()),1,10);
        set str =  concat(s1,'-',s2,'-',s3,'-',s4,'-',s5);
        insert into t1(id)values(str);
        set i=i+1;
    end while;
    COMMIT;
END$$
delimiter ;$$

# 3. insert data
insert into t1(id)values('1234567890-1234-1234-1234567890');
call bulk_insert1(11999);
--enable_query_log
--enable_result_log

# 4. test with tmp_table_size=2*1024*1024
set session internal_tmp_mem_storage_engine='memory';

set session tmp_table_size=2097152; # 2*1024*1024

select count(*) from ((select id from t1 limit 1000) union select 1) as t where id='1234567890-1234-1234-1234567890';

select count(*) from ((select id from t1 limit 12000) union select 1) as t where id='1234567890-1234-1234-1234567890';

select count(*) from ((select id from t1 limit 12000) union all select 1) as t where id='1234567890-1234-1234-1234567890';

# 5. test with tmp_table_size=16*1024*1024
set session tmp_table_size=16777216; # 16*1024*1024

select count(*) from ((select id from t1 limit 1000) union select 1) as t where id='1234567890-1234-1234-1234567890';

select count(*) from ((select id from t1 limit 12000) union select 1) as t where id='1234567890-1234-1234-1234567890';

select count(*) from ((select id from t1 limit 12000) union all select 1) as t where id='1234567890-1234-1234-1234567890';

--disable_query_log
--disable_result_log
# 6. cleanup
drop table t1;
drop PROCEDURE bulk_insert1;
--enable_query_log
--enable_result_log
```

wrong_result_with_union_and_small_memory_tmp_table.result
```
set session internal_tmp_mem_storage_engine='memory';
set session tmp_table_size=2097152;
select count(*) from ((select id from t1 limit 1000) union select 1) as t where id='1234567890-1234-1234-1234567890';
count(*)
1
select count(*) from ((select id from t1 limit 12000) union select 1) as t where id='1234567890-1234-1234-1234567890';
count(*)
0
select count(*) from ((select id from t1 limit 12000) union all select 1) as t where id='1234567890-1234-1234-1234567890';
count(*)
1
set session tmp_table_size=16777216;
select count(*) from ((select id from t1 limit 1000) union select 1) as t where id='1234567890-1234-1234-1234567890';
count(*)
1
select count(*) from ((select id from t1 limit 12000) union select 1) as t where id='1234567890-1234-1234-1234567890';
count(*)
1
select count(*) from ((select id from t1 limit 12000) union all select 1) as t where id='1234567890-1234-1234-1234567890';
count(*)
1
```

[29 Aug 2022 8:42] MySQL Verification Team

Hello mengchu shi,

Thank you for the report and feedback.

regards,
Umesh