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

    • 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

              igor Igor Babaev
              alice Alice Sherepa
              Votes:
              6 Vote for this issue
              Watchers:
              7 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.