[MDEV-32520] Query aliases accessible in subquery Created: 2023-10-19  Updated: 2023-10-27  Resolved: 2023-10-27

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Lou Mazzucchelli Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: compat80
Environment:

AWS


 Description   

This query works in MySQL, but will not run in MariaDB. Why?

select user_id as uID, last_name, first_name, athlete, parent, coach, code,
 
       sum(points) as total,
 
       propoints as lifetime,
 
       (select count(*) / 7 from 
       		(select count(user_id), Date(created_at) AS DATE
 
       		  from user_propoints
 
       		  where user_id = uID
 
       		  group by date) as ac1)
 
        as activity
 
from user_propoints
 
left join users on users.id = user_id
 
left join countries on users.country = countries.id
 
group by user_id
 
order by total desc

Output from MariaDB:

Error in query (1054): Unknown column 'uID' in 'where clause'



 Comments   
Comment by Sergei Golubchik [ 2023-10-27 ]

I'd say that MariaDB is correct and MySQL contradicts its own manual. Quoting https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column

and

Standard SQL disallows references to column aliases in a WHERE clause

So, only GROUP BY, ORDER BY, and HAVING clauses are valid places where you can use a column alias, not a WHERE clause of the subquery in the SELECT list.

Generated at Thu Feb 08 10:31:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.