Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
As MySQL (and MariaDB) support expressions in GROUP BY clause (see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html), it would be useful to support them in ONLY_FULL_GROUP_BY mode as well (taking the mode details into account).
Now (in 10.2) it seems even obviously logically correct queries are rejected, while in even more trivial cases expressions in GROUP BY work. Consider the following example:
MariaDB [test]> create table tt(id int primary key, a int, b int);
|
Query OK, 0 rows affected (0.28 sec)
|
|
MariaDB [test]> select @@sql_mode;
|
+-------------------------------------------------------------------------------------------+
|
| @@sql_mode |
|
+-------------------------------------------------------------------------------------------+
|
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
+-------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a,coalesce(b,1)*a from tt group by a,coalesce(b,1);
|
Empty set (0.00 sec)
|
|
MariaDB [test]> set sql_mode=only_full_group_by;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> select a,coalesce(b,1)*a from tt group by a,coalesce(b,1);
|
ERROR 1055 (42000): 'test.tt.b' isn't in GROUP BY
|
MariaDB [test]> select a, floor(b) from tt group by a, floor(b);
|
Empty set (0.01 sec)
|
|
MariaDB [test]> select a, floor(b)*2 from tt group by a, floor(b);
|
ERROR 1055 (42000): 'test.tt.b' isn't in GROUP BY
|
|
MariaDB [test]> select a, mod(b,2) from tt group by a, mod(b,2);
|
Empty set (0.00 sec)
|
|
MariaDB [test]> select a, 2*mod(b,2) from tt group by a, mod(b,2);
|
ERROR 1055 (42000): 'test.tt.b' isn't in GROUP BY
|
...
|
So, either we should reject all kinds of expressions in GROUP_BY when we use ONLY_FULL_GROUP_BY mode, or we should take functional dependencies on these expressions into account properly.
This is probably related to https://jira.mariadb.org/browse/MDEV-11588
See also upstream https://bugs.mysql.com/bug.php?id=86475