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

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

          links to

          Web Link StackOverflow

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            Affects Version/s 10.2.2 [ 22013 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Description Query returns error in ONLY_FULL_GROUP_BY sql_mode, while in "" returns correct results.
            {noformat}
            Non-grouping field 'ct' is used in HAVING clause
            {noformat}
            The error message is also incorrect, as there was no HAVING clause in query.

            {code:sql}
            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);
            {code}
            		Based on StackOverflow question posted by *Joyce Babu*
            https://stackoverflow.com/questions/45146830/non-grouping-field-used-in-having-clause-with-sub-query

            Query returns error in ONLY_FULL_GROUP_BY sql_mode, while in "" returns correct results.
            {noformat}
            Non-grouping field 'ct' is used in HAVING clause
            {noformat}
            The error message is also incorrect, as there was no HAVING clause in query.

            {code:sql}
            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);
            {code}
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova made changes -
            Component/s Data Manipulation - Subquery [ 10107 ]
            Fix Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Assignee Igor Babaev [ igor ]
            Description Based on StackOverflow question posted by *Joyce Babu*
            https://stackoverflow.com/questions/45146830/non-grouping-field-used-in-having-clause-with-sub-query

            Query returns error in ONLY_FULL_GROUP_BY sql_mode, while in "" returns correct results.
            {noformat}
            Non-grouping field 'ct' is used in HAVING clause
            {noformat}
            The error message is also incorrect, as there was no HAVING clause in query.

            {code:sql}
            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);
            {code}
            		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:
            {noformat}
            Non-grouping field 'ct' is used in HAVING clause
            {noformat}

            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.

            {code:sql}
            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);
            {code}
            Summary Improper error message in ONLY_FULL_GROUP_BY sql_mode Improper error in ONLY_FULL_GROUP_BY sql_mode with condition_pushdown_for_derived=on
            serg Sergei Golubchik made changes -
            Sprint 10.2.11 [ 203 ]
            alice Alice Sherepa added a comment - - edited 

             
            		 
            MariaDB [test]> select version();
            		 
            +----------------+
            		 
            | version()      |
            		 
            +----------------+
            		 
            | 10.2.2-MariaDB |
            		 
            +----------------+
            		 
            1 row in set (0.00 sec)
            		 
             
            		 
            MariaDB [test]> SET  sql_mode = 'ONLY_FULL_GROUP_BY';
            		 
            Query OK, 0 rows affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> explain extended SELECT * FROM t1 
                ->   LEFT OUTER JOIN 
                ->   (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2)
            		 
                -> WHERE (vc.ct>0);
            		 
            ERROR 1463 (42000): Non-grouping field 'ct' is used in HAVING clause
            		 
             
            		 
            MariaDB [test]> SET optimizer_switch='condition_pushdown_for_derived=off';
            		 
            Query OK, 0 rows affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> explain extended SELECT * FROM t1 
                ->   LEFT OUTER JOIN 
                ->   (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2)
            		 
                -> WHERE (vc.ct>0);
            		 
            +------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+
            		 
            | id   | select_type | table      | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra                           |
            		 
            +------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+
            		 
            |    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL        |    8 |   100.00 | Using where                     |
            		 
            |    1 | PRIMARY     | <derived2> | ref  | key0          | key0 | 5       | test.t1.id2 |    2 |   100.00 | Using where                     |
            		 
            |    2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL        |    6 |   100.00 | Using temporary; Using filesort |
            		 
            +------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+
            		 
            3 rows in set, 1 warning (0.00 sec)

            alice Alice Sherepa added a comment - - edited   MariaDB [test]> select version(); +----------------+ | version() | +----------------+ | 10.2.2-MariaDB | +----------------+ 1 row in set (0.00 sec)   MariaDB [test]> SET sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> explain extended SELECT * FROM t1 -> LEFT OUTER JOIN -> (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2) -> WHERE (vc.ct>0); ERROR 1463 (42000): Non-grouping field 'ct' is used in HAVING clause   MariaDB [test]> SET optimizer_switch='condition_pushdown_for_derived=off'; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> explain extended SELECT * FROM t1 -> LEFT OUTER JOIN -> (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2) -> WHERE (vc.ct>0); +------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t1.id2 | 2 | 100.00 | Using where | | 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary; Using filesort | +------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+ 3 rows in set, 1 warning (0.00 sec)
            igor Igor Babaev (Inactive) added a comment -

            The patch for bug mdev-14368 that was pushed into 5.5 fixes the problem.
            I've checked this when applying the patch to to 10.2.

            igor Igor Babaev (Inactive) added a comment - The patch for bug mdev-14368 that was pushed into 5.5 fixes the problem. I've checked this when applying the patch to to 10.2.
            igor Igor Babaev (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            igor Igor Babaev (Inactive) added a comment -

            This bug is a consequence of the bug mdev-14368 fixed in 5.5.59.

            igor Igor Babaev (Inactive) added a comment - This bug is a consequence of the bug mdev-14368 fixed in 5.5.59.
            igor Igor Babaev (Inactive) added a comment -

            Fixed after a upstream merge from 5.5 where the cause of the bug was removed.

            igor Igor Babaev (Inactive) added a comment - Fixed after a upstream merge from 5.5 where the cause of the bug was removed.
            igor Igor Babaev (Inactive) made changes -
            issue.field.resolutiondate 2018-01-05 18:23:06.0 2018-01-05 18:23:06.647
            igor Igor Babaev (Inactive) made changes -
            Fix Version/s 10.2.12 [ 22810 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 81916 ] MariaDB v4 [ 152570 ]

            People

              igor Igor Babaev (Inactive)
              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.