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'

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.0
    • Fix Version/s: 5.5.59
    • Component/s: Optimizer
    • Labels:
      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

            Assignee:
            igor Igor Babaev
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: