Bug #100734 Select with group by returns different results
Submitted: 3 Sep 2020 17:19 Modified: 3 Oct 2020 20:16
Reporter: Ross Barbieri Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.021 OS:Any (Tried on windows, mac, centos)
Assigned to: CPU Architecture:x86

[3 Sep 2020 17:19] Ross Barbieri
The sql statement below returns different results on 5.7 and 8.021. When I run it twice on 8.021 it returns correct results initially, but all subsequent results returns a different/incorrect result.

select year(event_date) as y,
		month(event_date) as m,
		count(distinct principal) as number
from jhi_persistent_audit_event
where year(event_date) = 2020
and principal in (select distinct email 
				  from jhi_user 
				  where month(created_date) = 1
				  and year(created_date) = 2020)
group by year(event_date),month(event_date);

In this case the group by doesn't respect the second group by term (month(event_date)).

How to repeat:
Here is a link to video to demonstrate:


The first time it runs I get the correct result, the second time I run it I get the second/incorrect result. If I run this on the original 5.7 database I get the correct result consistently.

Suggested fix:
If I restart the mysql service (windows) it resets and will run correctly one time.
[3 Sep 2020 20:16] MySQL Verification Team
Thank you for the bug report. Please provide the complete SQL script test case (create table, insert data, query, real result and expected one) instead of video. Thanks.
[4 Oct 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".