Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.5, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
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.
Thanks a lot for the report and the test case!
Reproducible on 10.2-10.4, MyIsam/Innodb
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`