Bug #118398 Using temporary with avg cause wrong result

• Submitted: 9 Jun 8:00
• Modified: 11 Jun 12:39
• Reporter: haizhen xue
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[9 Jun 8:00] haizhen xue

Description:
The following two SQL statements are equivalent, but the execution results are different.

mysql> SELECT tt2.bool_col,AVG(tt2.time_col) col2 FROM (select distinct bool_col, time_col from tbl_1_in_list where bool_col=0 or bool_col =2) tt1 join tbl_1_in_list tt2 on tt1.bool_col=tt2.bool_col GROUP BY 1;

+----------+------------------+
| bool_col | col2             |
+----------+------------------+
|        0 | 86666.6666666667 |
|        2 |             NULL |
+----------+------------------+
2 rows in set (1.11 sec)

mysql>
mysql> SELECT bool_col,AVG(time_col)  col2 FROM tbl_1_in_list WHERE bool_col IN (0,2) GROUP BY 1;
+----------+------------------+
| bool_col | col2             |
+----------+------------------+
|        0 | 86666.6666666660 |
|        2 |             NULL |
+----------+------------------+
2 rows in set (1.37 sec)

How to repeat:
create database test;
use test;
source datafile/tbl_1_in_list.sql

SELECT bool_col,AVG(time_col)  col2 FROM tbl_1_in_list WHERE bool_col IN (0,2) GROUP BY 1;

SELECT tt2.bool_col,AVG(tt2.time_col) col2 FROM (select distinct bool_col, time_col from tbl_1_in_list where bool_col=0 or bool_col =2) tt1 join tbl_1_in_list tt2 on tt1.bool_col=tt2.bool_col GROUP BY 1;

[9 Jun 8:04] haizhen xue

tar -zxvf tbl_1_in_list.rar
create database test;
use test;
source tbl_1_in_list.sql

SELECT tt2.bool_col,AVG(tt2.time_col) col2 FROM (select distinct bool_col, time_col from tbl_1_in_list where bool_col=0 or bool_col =2) tt1 join tbl_1_in_list tt2 on tt1.bool_col=tt2.bool_col GROUP BY 1;

SELECT bool_col,AVG(time_col)  col2 FROM tbl_1_in_list WHERE bool_col IN (0,2) GROUP BY 1;

[11 Jun 12:39] MySQL Verification Team

Hello haizhen xue,

Thank you for the report and test case.

regards,
Umesh