2

I've upgraded from MySQL 5.5 to 5.7 and I have a query that no longer works. It's quite a big one but I've reduced it down to this - the query itself doesn't really make sense but demonstrates:

SELECT
id AS id2,
name,
SUM(id * (SELECT 10 FROM event WHERE event.product_id=id2 LIMIT 1))
FROM product
GROUP BY product.category_id

This works in 5.5 but not in 5.7 (I get [Err] 1054 - Unknown column 'id2' in 'where clause'). It works if I don't alias the column:

SELECT
id,
name,
SUM(id * (SELECT 10 FROM event WHERE event.product_id=product.id LIMIT 1))
FROM product
GROUP BY product.category_id

But in the real query it's quite a complicated expression so I want to alias it. It also works in 5.7 if I exclude the aggregate function:

SELECT
id,
name,
id * (SELECT 10 FROM event WHERE event.product_id=product.id LIMIT 1)
FROM product
GROUP BY product.category_id

I cant find anything in the docs that explains this discrepancy. I can rewrite the query but it will be a lot messier so I'm trying to figure out a way if I can re-enable this behaviour (note SQL_MODE is the same between versions).

Matthew
  • 31
  • 1
  • 3
  • You can't use an alias in a where clause: https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql – Honeyboy Wilson May 22 '18 at 15:57
  • The docs explain the discrepancy. Are you more interested in an explanation or a solution? If the latter, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry May 22 '18 at 16:03
  • @Matthew Because MySQL really doesn't know what `id2` is. Use `product.id` instead. – Eric May 22 '18 at 16:42
  • You can only use aliases from the SELECT in HAVING, ORDER BY, and GROUP BY* clauses, or an enclosing query (i.e. can use an alias from a subquery outside of it). _*Exception: You cannot use the alias of an aggregate result in a GROUP BY._ – Uueerdo May 22 '18 at 16:53
  • @HoneyboyWilson thanks for the reply. I realise I generally can't use an alias in the WHERE, so I was a bit surprised looking at this older query that it did seem to work in a subquery - not sure what leap of logic I used at the time but it's a much larger query than the example and using the alias this way makes it much neater. – Matthew May 22 '18 at 19:35
  • @Strawberry TBH I'm more interested in an explanation - I have a few other ways I can write the (very long) query, but not as neat as it is currentlly. So maybe my query is why did it work before - since as others suggested, the expected behaviour is it not to do so. Did you find any reference in the MySQL docs? – Matthew May 22 '18 at 19:39
  • It seems to me that this covers it: https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html – Strawberry May 22 '18 at 22:58

0 Answers0