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

Improper error for a grouping query that uses alias in HAVING when sql_mode = 'ONLY_FULL_GROUP_BY'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 5.5.59
    • Optimizer
    • None

    Description

      If a query with GROUP BY contains an aggregate function with an alias and this alias
      is used in the HAVING clause of the query the server reports an error:

      MariaDB [test]> SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0;
      		 
      ERROR 1463 (42000): Non-grouping field 'ct' is used in HAVING clause

      The following test case demonstrates the problem:

      SET  sql_mode = 'ONLY_FULL_GROUP_BY';
      		 
       
      create table t1(a int);
      		 
      insert  t1 values (4),(1),(2),(1), (3),(4);
      		 
       
      		 
      SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0;

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.