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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.51b | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[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.
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 ??