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)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.51b
• OS: Any
• 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   
éáú