MySQL Bugs: #106539: Wrong result for aggregate function.

Bug #106539	Wrong result for aggregate function.
Submitted:	22 Feb 2022 17:56	Modified:	7 Mar 2022 9:56
Reporter:	Luuk V	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, having, min

Description:
the result from this statement is wrong, when there are many (?) records in the table.

select t1.a, min(t2.c) 
from test123 t1 
left join test123 t2 on t2.b=t1.a 
group by t1.a 
having t1.a=2;

a DBFIDDLE with this is created here: 
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=75a221b6ea180308aeacae83c107d5cd

How to repeat:
create table test123(a int, b int,c int);

insert into test123 values (rand()*1000,rand()*1000,rand()*1000);
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
insert into test123 select rand()*1000,rand()*1000,rand()*1000 from test123;
-- repeat above statement until next statement gives result ;)

select t1.a, min(t2.c) 
from test123 t1 
left join test123 t2 on t2.b=t1.a 
group by t1.a 
having t1.a=2;

Suggested fix:

The final result should also be visible in this query:

select * from test123 where a=2 and b=2

Hello Luuk V,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
This is not a bug, the handling is correct.
What happens is this:
* When the “a” column has no value 2, the result is empty.
* When the “a” column has some value 2, but the “b” column does not, the left join is NULL-extended and min(t2.c) is NULL
* When both “a” and “b” columns have some value 2, this is a regular join, and we find the real minimum of “c”.

When changing the LEFT JOIN to INNER JOIN, i still get a result, and the last statement ("select a,b,c from test123 where a=2 and b=2;") does not return a result?

[test]> select t1.a, t2.b, min(t2.c) from test123 t1 left join test123 t2 on t2.b=t1.a where t1.a=2 group by t1.a having t1.a=2;
+------+------+-----------+
| a    | b    | min(t2.c) |
+------+------+-----------+
|    2 |    2 |       360 |
+------+------+-----------+
1 row in set (0.00 sec)

[test]> select t1.a, t2.b, min(t2.c) from test123 t1 inner join test123 t2 on t2.b=t1.a where t1.a=2 group by t1.a having t1.a=2;
+------+------+-----------+
| a    | b    | min(t2.c) |
+------+------+-----------+
|    2 |    2 |       360 |
+------+------+-----------+
1 row in set (0.00 sec)

[test]> select t1.a, t2.b, min(t2.c) from test123 t1 inner join test123 t2 on t2.b=t1.a group by t1.a having t1.a=2;
+------+------+-----------+
| a    | b    | min(t2.c) |
+------+------+-----------+
|    2 |    2 |       360 |
+------+------+-----------+
1 row in set (0.01 sec)

[test]> select a,b,c from test123 where a=2 and b=2;
Empty set (0.00 sec)

[test]>

[test]> select a,b,c from test123 where a=2 or b=2 order by c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |  860 |  296 |
|    2 |  465 |  319 |
|    2 |  268 |  332 |
|  883 |    2 |  360 |
|  163 |    2 |  521 |
|  161 |    2 |  527 |
|  805 |    2 |  597 |
|    2 |  122 |  603 |
|    2 |  945 |  719 |
|  717 |    2 |  860 |
|    2 |  774 |  863 |
|  690 |    2 |  939 |
+------+------+------+
12 rows in set (0.00 sec)

Export from my 'test123' table

Attachment: test123.sql (application/octet-stream, text), 56.64 KiB.

It happens because you are performing a self join on the table.

Ok, thanks, now I see:
select b, min(c) from test123 where b=2; ==> 360