MySQL Bugs: #100734: Select with group by returns different results

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:	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

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.

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.

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