[MDEV-17796] WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y) with GROUP BY + ORDER BY Created: 2018-11-22  Updated: 2019-04-25  Resolved: 2019-04-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.5, 10.2, 10.3, 10.4
Fix Version/s: 10.2.24, 10.3.15, 10.4.5

Type: Bug Priority: Major
Reporter: Berengar Lehr Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

docker container

Attachments: File test.sql    

 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.

 Comments   
Comment by Alice Sherepa [ 2018-11-26 ]

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`

Comment by Igor Babaev [ 2019-04-15 ]

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.

Comment by Igor Babaev [ 2019-04-15 ]

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

Comment by Berengar Lehr [ 2019-04-23 ]

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.

Comment by Sergei Golubchik [ 2019-04-23 ]

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.

Comment by Igor Babaev [ 2019-04-24 ]

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 |
 
+------+

Comment by Igor Babaev [ 2019-04-24 ]

A fix for this bug was pushed into 10.2

Generated at Thu Feb 08 08:39:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.