Bug #37463 COLLATE possible in ORDER BY and GROUP BY, but not in both at same time

Submitted: 17 Jun 2008 21:33 Modified: 17 Jun 2008 21:55
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.51b OS:Any
Assigned to: CPU Architecture:Any

[17 Jun 2008 21:33] Peter Laursen
Description:
COLLATE possible in ORDER BY and GROUP BY, but not in both at same time.

How to repeat:
DROP TABLE IF EXISTS `t1`;

CREATE TABLE t1 (                                    
          id bigint(20) NOT NULL auto_increment,             
          t varchar(20) default NULL,                        
          PRIMARY KEY  (`id`)                                  
        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `t1` (t) values ('éáú'),('eau'),('abc'),('ábc');

select t from t1 order by t collate utf8_danish_ci; -- 4 rows in result
select t from t1 order by t collate utf8_bin; --  4 rows in result but now the values with accents come last

select t from t1 group by t collate utf8_danish_ci; -- 2 rows in result
select t from t1 group by t collate utf8_bin; -- 4 rows in result

-- so far so god but now 

select t from t1 order by t collate utf8_bin group by t collate utf8_danish;

/* returns

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by t collate utf8_bin' ..."

*/

Suggested fix:
I think it makes sense to COLLATE (even with different colations) in both ORDER BY and GROUP BY (but the same error comes if same collation is used).

I expected the result

t     
------
abc   
eau   

*/

but am I wrong here ??
[17 Jun 2008 21:55] Peter Laursen
how silly .. please disregard

select t from t1  group by t collate utf8_danish_ci order by t collate utf8_bin;

works and returns

t     
------
abc   
éáú

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.