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

Improper error in ONLY_FULL_GROUP_BY sql_mode with condition_pushdown_for_derived=on

    XMLWordPrintable

    Details

    • Sprint:
      10.2.11

      Description

      Based on StackOverflow question posted by Joyce Babu
      https://stackoverflow.com/questions/45146830/non-grouping-field-used-in-having-clause-with-sub-query

      With condition_pushdown_for_derived=on (which is default in 10.2) and sql_mode=ONLY_FULL_GROUP_BY, the query from the test case below returns an error:

      Non-grouping field 'ct' is used in HAVING clause
      

      The query appears to be valid, and without ONLY_FULL_GROUP_BY and/or with condition_pushdown_for_derived=off it works.

      The error message is also incorrect, as there was no HAVING clause in query.

      SET  sql_mode = 'ONLY_FULL_GROUP_BY';
       
      create table t1 (id int,id2 int);
      insert into t1 values (1,1), (2,3),(3,4), (7,2);
       
      create table t2(id2 int);
      insert  t2 values (1),(2),(3);
       
      SELECT * FROM t1 
        LEFT OUTER JOIN 
        (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2)
      WHERE (vc.ct>0);
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              alice Alice Sherepa
              Votes:
              6 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: