MySQL Bugs: #110011: inconsistent behavior with group by and order by

Bug #110011	inconsistent behavior with group by and order by
Submitted:	9 Feb 2023 15:20	Modified:	13 Feb 2023 13:40
Reporter:	Matthew Macarty	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
GROUP BY monthname([value])
ORDER BY month([value]);

and 

GROUP BY dayname([value])
ORDER BY weekday([value]);

Works as expected in 8.0.31, does not appear to work in 8.0.32

How to repeat:
Try this in 8.0.32

use sakila;

select monthname(payment_date), sum(amount)
from payment
group by monthname(payment_date)
order by month(payment_date);

and 

select dayname(payment_date), sum(amount)
from payment
group by dayname(payment_date)
order by weekday(payment_date);

Hi Mr. Macarty,

Thank you for your bug report.

Please, provide us with a full test case, including the table(s), so that we can attempt to reproduce what you are observing.

Waiting on your feedback.

use sakila;

select monthname(payment_date), sum(amount)
from payment
group by monthname(payment_date)
order by month(payment_date);

Hi Mr. Macarty,

We used ONLY_FULL_GROUP_BY, since this is defined by standards and got the following results:

mysql> select payment_date, monthname(payment_date), sum(amount) from payment group by monthname(payment_date) order by month(payment_date);
+---------------------+-------------------------+-------------+
| payment_date        | monthname(payment_date) | sum(amount) |
+---------------------+-------------------------+-------------+
| 2006-02-14 15:16:03 | February                |      514.18 |
| 2005-05-25 11:30:37 | May                     |     4823.44 |
| 2005-06-15 00:54:12 | June                    |     9629.89 |
| 2005-07-08 03:17:05 | July                    |    28368.91 |
| 2005-08-01 08:51:04 | August                  |    24070.14 |
+---------------------+-------------------------+-------------+
5 rows in set (0.22 sec)

mysql> select payment_date, dayname(payment_date), sum(amount) from payment group by monthname(payment_date) order by month(payment_date);
+---------------------+-----------------------+-------------+
| payment_date        | dayname(payment_date) | sum(amount) |
+---------------------+-----------------------+-------------+
| 2006-02-14 15:16:03 | Tuesday               |      514.18 |
| 2005-05-25 11:30:37 | Wednesday             |     4823.44 |
| 2005-06-15 00:54:12 | Wednesday             |     9629.89 |
| 2005-07-08 03:17:05 | Friday                |    28368.91 |
| 2005-08-01 08:51:04 | Monday                |    24070.14 |
+---------------------+-----------------------+-------------+
5 rows in set (0.23 sec)

What results do you get with the queries like above ???

Next, what did you set your sql_mode to ?????

We are waiting on your feedback.

I see. So is ONLY_FULL_GROUP_BY disabled by default in 8.0.32?

Hi Mr. Macarty,

No, it is not disabled in 8.0.32. All our packages contain binaries whose sql_mode obligatory contains ONLY_FULL_GROUP_BY.

Or , as it is written in our Reference Manual:

"
The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

"