Re: Where clause doesn't work! Is this a bug?

[Peter Brawley]

My (reading) error, the aliases are in subqueries and referenced from an outer query, so the Where clause can see them fine.

When case sensitivity issues are resolved, your query executes correctly in MySQL 5.5, 5.7 and 8.0.3, and in MariaDB 10.0 and 10.2. It gives your incorrect result in 5.6.

So evidently a bug was introduced in 5.6, fixed later in 5.6 (the 5.6 build I have access to right now is 5.6.17) or in 5.7. You'll very likely find a report in the version release notes.

Re case sensitivity: varying case from DDL to queries works in Windows because Windows is case-permissive. Not in Linux, which is case-sensitive. So it's not good practice. And while all-upper-case is supported, it is not common, hard to read, and creates portability issues. Consider using lower case for all MySQL identifier names.