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

[3 Sep 17:19] Ross Barbieri
Description:
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:

https://screenrec.com/share/hK96Hi58dX

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 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 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".