Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32520

Query aliases accessible in subquery

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • None
    • N/A
    • Parser
    • 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'

      Attachments

        Issue Links

          links to

          Web Link SO post

          Activity

            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.

            People

              serg Sergei Golubchik
              loumazz Lou Mazzucchelli
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.