Bug #89621 ORDER BY BINARY on alias throws Error:1247
Submitted: 12 Feb 2018 1:10 Modified: 13 Feb 2018 17:32
Reporter: Bernie van't Hof Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.17 OS:MacOS (10.13.3)
Assigned to: CPU Architecture:Any
Tags: ORDER BY BINARY

[12 Feb 2018 1:10] Bernie van't Hof 
Description:
Why the inconsistency?:

SELECT SUM(`Amt`) AS `Mycalc`
FROM `transact`
ORDER BY `Mycalc` 

OK (no BINARY)

SELECT SUM(`Amt`)
FROM `transact`
ORDER BY BINARY SUM(`Amt`)
 
OK (using BINARY)

SELECT SUM(`Amt`) AS `Mycalc`
FROM `transact`
ORDER BY BINARY `Mycalc` 

Error Code: 1247. Reference 'Mycalc' not supported (reference to group function)
(using BINARY on alias)

How to repeat:
CREATE TABLE `TRANSACT` (
   `Amt` decimal(19,4) DEFAULT NULL,
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT SUM(`Amt`) AS `Mycalc`
FROM `transact`
ORDER BY BINARY `Mycalc`
[13 Feb 2018 17:32] MySQL Verification Team 
Hi!

I have repeated your behaviour and I think it is a bug.

However, as there is easy workaround, this is not a high priority bug.

Verified as reported.