Bug #117670 Unknown column '?' in 'group statement'
Submitted: 12 Mar 3:46 Modified: 12 Mar 13:48
Reporter: kim kyoung youn Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.34, 8.0.41 OS:Linux
Assigned to: CPU Architecture:Any
Tags: Optimizer, regression

[12 Mar 3:46] kim kyoung youn 
Description:
- In 8.0.32, there is no processing error for incorrect column names in the group by clause,
but an error occurs in 8.0.40.
- From 8.0.34 to 8.0.40, all replay tests result in ERROR 1054 (42S22): Unknown column 'a.sdate' in 'group statement'.
The same error does not occur until 8.0.33.

How to repeat:
The reproducible query uses the following query:

select
a.dt AS sdate
,a.user, a.host
from (
select DATE_FORMAT('2025-03-11', '%Y-%m-%d') AS dt, user, host from mysql.user
) a
group by a.sdate;

MySQL> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

MySQL> select
    -> a.dt AS sdate 
    -> ,a.user, a.host
    -> from (
    -> select DATE_FORMAT('2025-03-11', '%Y-%m-%d') AS dt, user, host from mysql.user
    -> ) a
    -> group by a.sdate;
+------------+--------+------+
| sdate      | user   | host |
+------------+--------+------+
| 2025-03-11 | user12 | %    |
+------------+--------+------+
1 row in set (0.01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.40    |
+-----------+
1 row in set (0.00 sec)

mysql> select
    -> a.dt AS sdate 
    -> ,a.user, a.host
    -> from (
    -> select DATE_FORMAT('2025-03-11', '%Y-%m-%d') AS dt, user, host from mysql.user
    -> ) a
    -> group by a.sdate;
ERROR 1054 (42S22): Unknown column 'a.sdate' in 'group statement'
[12 Mar 6:15] MySQL Verification Team 
Hello Kim kyoung,

Thank you for the report and feedback.

regards,
Umesh
[12 Mar 13:48] Roy Lyseng 
Posted by developer:
 
The query has an error and is therefore rejected.
The clause "group by a.sdate" indicates that the table "a" has a column "sdate".
But there is no such column. Instead, this expression is matched against
the alias "sdate". However, it should only be possible to match against this
alias when no prefix is given.
Before MySQL 8.0.34, "a.sdate" was erroneously resolved as the alias "sdate".
The problem was corrected in 8.0.34.

The proper fix is probably to replace "a.sdate" with "sdate" in the query.