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

WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y) with GROUP BY + ORDER BY

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.5, 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.2.24, 10.3.15, 10.4.5
    • Optimizer
    • None
    • docker container

    Description

      Run the attached sql and you will find a query that returns rows that where explicitly filtered.

      … WHERE group = 2 …
      group	values
      1	A
      2	B
      3	1
      

      The issue relates to versions, including 10.2.[5-18] 10.3 10.4 but not 10.0.[26-38], 10.1 and 5.5 (tested with the latest docker releases). It does not appear in all mysql versions (5.5, 5.6, 5.7) but mysql_5.7 returns the following error:

      ERROR 1055 (42000) at line 28: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'test.bug.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
      

      (line 28 is the beginning of the SELECT stmt.

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          Thanks a lot for the report and the test case!
          Reproducible on 10.2-10.4, MyIsam/Innodb

          CREATE TABLE t1 (id int, gr int, v1 varchar(10));
          INSERT INTO t1 (id, gr, v1) VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL);
           
          SELECT DISTINCT IFNULL(GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          SELECT DISTINCT IF(1=1,GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          

          10.3:
          MariaDB [test]> SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), '1') FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          +-------------------------------+
          | NULLIF(GROUP_CONCAT(v1), '1') |
          +-------------------------------+
          | NULL                          |
          | A                             |
          | B                             |
          +-------------------------------+
          3 rows in set (0.001 sec)
           
          MariaDB [test]> explain extended SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), '1') FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
          | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
          |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using temporary; Using filesort |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
          1 row in set, 1 warning (0.000 sec)
           
          Note (Code 1003): select distinct nullif(<cache>(group_concat(`test`.`t1`.`v1` separator ',')),'1') AS `NULLIF(GROUP_CONCAT(v1), '1')` from `test`.`t1` where `test`.`t1`.`gr` = 2 group by `test`.`t1`.`id` order by `test`.`t1`.`v1`
           
          10.1:
          MariaDB [test]> explain extended SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), '1') FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
          | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
          |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using temporary; Using filesort |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
          1 row in set, 1 warning (0.00 sec)
           
          Note (Code 1003): select distinct nullif(<cache>(group_concat(`test`.`t1`.`v1` separator ',')),'1') AS `NULLIF(GROUP_CONCAT(v1), '1')` from `test`.`t1` where (`test`.`t1`.`gr` = 2) group by `test`.`t1`.`id` order by `test`.`t1`.`v1`
          
          

          alice Alice Sherepa added a comment - Thanks a lot for the report and the test case! Reproducible on 10.2-10.4, MyIsam/Innodb CREATE TABLE t1 (id int , gr int , v1 varchar (10)); INSERT INTO t1 (id, gr, v1) VALUES (1,1, 'A' ), (2,2, 'B' ), (3,3, NULL );   SELECT DISTINCT IFNULL(GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; SELECT DISTINCT IF (1=1,GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; SELECT DISTINCT NULLIF (GROUP_CONCAT(v1), null ) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; 10.3: MariaDB [test]> SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), '1') FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; +-------------------------------+ | NULLIF(GROUP_CONCAT(v1), '1') | +-------------------------------+ | NULL | | A | | B | +-------------------------------+ 3 rows in set (0.001 sec)   MariaDB [test]> explain extended SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), '1') FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.000 sec)   Note (Code 1003): select distinct nullif(<cache>(group_concat(`test`.`t1`.`v1` separator ',')),'1') AS `NULLIF(GROUP_CONCAT(v1), '1')` from `test`.`t1` where `test`.`t1`.`gr` = 2 group by `test`.`t1`.`id` order by `test`.`t1`.`v1`   10.1: MariaDB [test]> explain extended SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), '1') FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using temporary; Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------+ 1 row in set, 1 warning (0.00 sec)   Note (Code 1003): select distinct nullif(<cache>(group_concat(`test`.`t1`.`v1` separator ',')),'1') AS `NULLIF(GROUP_CONCAT(v1), '1')` from `test`.`t1` where (`test`.`t1`.`gr` = 2) group by `test`.`t1`.`id` order by `test`.`t1`.`v1`
          igor Igor Babaev added a comment -

          The following query works fine:

           
          MariaDB [test]> SELECT DISTINCT IFNULL(GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id;
          +----------------------------+
          | IFNULL(GROUP_CONCAT(v1),1) |
          +----------------------------+
          | B                          |
          +----------------------------+
          

          The query

          SELECT DISTINCT IFNULL(GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1;
          

          is one with non-deterministic semantic: in ORDER BY v1 does not functionally depend on id.

          igor Igor Babaev added a comment - The following query works fine: MariaDB [test]> SELECT DISTINCT IFNULL(GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id; +----------------------------+ | IFNULL(GROUP_CONCAT(v1),1) | +----------------------------+ | B | +----------------------------+ The query SELECT DISTINCT IFNULL(GROUP_CONCAT(v1),1) FROM t1 WHERE gr=2 GROUP BY id ORDER BY v1; is one with non-deterministic semantic: in ORDER BY v1 does not functionally depend on id.
          igor Igor Babaev added a comment -

          The explanation why it's not a bug see in Igor's comment

          igor Igor Babaev added a comment - The explanation why it's not a bug see in Igor's comment
          Berengar Berengar Lehr added a comment -

          I'm sorry, I don't get it. Either it's for some reason I absolutely not yet don't understand an invalid query (as the error in mysql, not in mariadb, indicates).
          Or the query returns an unexpected (wrong) result.
          Either way, the current situations doesn't look bug free to me.

          Berengar Berengar Lehr added a comment - I'm sorry, I don't get it. Either it's for some reason I absolutely not yet don't understand an invalid query (as the error in mysql, not in mariadb, indicates). Or the query returns an unexpected (wrong) result. Either way, the current situations doesn't look bug free to me.

          igor, I don't quite agree. You are right that the semantic is non-deterministic. And there is no guarantee what v1 value out of all rows for a particular group of id will be used for ORDER BY.

          But it still does not mean that values filtered out by WHERE could appear in the result set. WHERE is applied first, then GROUP BY and ORDER BY. And in this query WHERE removes rows from the result set quite deterministically, as far as I can see.

          serg Sergei Golubchik added a comment - igor , I don't quite agree. You are right that the semantic is non-deterministic. And there is no guarantee what v1 value out of all rows for a particular group of id will be used for ORDER BY . But it still does not mean that values filtered out by WHERE could appear in the result set. WHERE is applied first, then GROUP BY and ORDER BY. And in this query WHERE removes rows from the result set quite deterministically, as far as I can see.
          igor Igor Babaev added a comment - - edited

          The following query that respects functional dependencies from grouping columns

          SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) as A FROM t1
             WHERE gr=2
           GROUP BY id
           ORDER BY id+1;
          

          still returns a wrong result set:

          MariaDB [test]> SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) as A FROM t1
              ->    WHERE gr=2
              ->  GROUP BY id
              ->  ORDER BY id+1;
          +------+
          | A    |
          +------+
          | A    |
          | B    |
          | NULL |
          +------+
          

          igor Igor Babaev added a comment - - edited The following query that respects functional dependencies from grouping columns SELECT DISTINCT NULLIF (GROUP_CONCAT(v1), null ) as A FROM t1 WHERE gr=2 GROUP BY id ORDER BY id+1; still returns a wrong result set: MariaDB [test]> SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) as A FROM t1 -> WHERE gr=2 -> GROUP BY id -> ORDER BY id+1; +------+ | A | +------+ | A | | B | | NULL | +------+
          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.2

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2

          People

            igor Igor Babaev
            Berengar Berengar Lehr
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.