[MDEV-27310] ONLY_FULL_GROUP_BY doesn't prevent ORDER BY from using a column missing in GROUP BY Created: 2021-12-19  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: upstream-fixed


 Description   

Please fix "Fix Version/s" as needed, if needed, I'm not sure in which versions it can and should be fixed. MySQL apparently fixed it in 5.6+, but whether it was before or after 5.6 became GA, I can't say.

set sql_mode=only_full_group_by;
 
 
 
create table t (a int, b int, c int);
 
insert into t values (1,2,3),(4,5,6);
 
select a, min(b) from t group by a order by c, a;
 
 
 
drop table t;

10.7 f5ecaf23

 
set sql_mode=only_full_group_by;
 
create table t (a int, b int, c int);
 
insert into t values (1,2,3),(4,5,6);
 
select a, min(b) from t group by a order by c, a;
 
a	min(b)
 
1	2
 
4	5

Active versions of MySQL prohibit it:

MySQL 5.6.51

 
set sql_mode=only_full_group_by;
 
create table t (a int, b int, c int);
 
insert into t values (1,2,3),(4,5,6);
 
bug.t                                    [ fail ]
 
        Test ended at 2021-12-19 20:40:36
 
 
 
CURRENT_TEST: bug.t
 
mysqltest: At line 5: query 'select a, min(b) from t group by a order by c, a' failed: 1055: 'test.t.c' isn't in GROUP BY

So does PostgreSQL:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
 
Type "help" for help.
 
 
 
postgres=# drop table t;
 
ERROR:  table "t" does not exist
 
postgres=# create table t (a int, b int, c int);
 
CREATE TABLE
 
postgres=# insert into t values (1,2,3),(4,5,6);
 
INSERT 0 2
 
postgres=# select a, min(b) from t group by a order by c, a;
 
ERROR:  column "t.c" must appear in the GROUP BY clause or be used in an aggregate function
 
LINE 1: select a, min(b) from t group by a order by c, a;


Generated at Thu Feb 08 09:51:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.