Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.9
-
None
Description
There is a rule for aggregated queries which says that SELECT expressions can only reference columns that were used to group by or were aggregated on. Sql mode ONLY_FULL_GROUP_BY enforces this rule. When this mode is activated there will be an error for queries that reference columns in SELECT expressions that were not used to group by. I found a false positive though:
CREATE TABLE test (prefix VARCHAR(255) NOT NULL, suffix VARCHAR(255) NOT NULL); |
INSERT INTO test(prefix, suffix) VALUES ('abc', 'def'), ('abc', 'def'), ('abc', 'xyz'), ('ab', 'cdef'); |
This is a valid query
SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; |
SELECT CONCAT(prefix, suffix), COUNT(*) FROM test GROUP BY CONCAT(prefix, suffix);
|
This query has an additional constant. It is also valid:
SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; |
SELECT CONCAT(prefix, suffix, 'constant'), COUNT(*) FROM test GROUP BY 1; |
Clients should not need to write a position, though.These queries fail, even though the rule is still adhered to:
SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; |
SELECT CONCAT(prefix, suffix, 'constant'), COUNT(*) FROM test GROUP BY CONCAT(prefix, suffix, 'constant'); |
SELECT CONCAT(prefix, suffix, 'constant'), COUNT(*) FROM test GROUP BY CONCAT(prefix, suffix); |
And this query also fails:
SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; |
SELECT prefix, COUNT(*) FROM test GROUP BY CONCAT(prefix, suffix);
|
Attachments
Issue Links
- relates to
-
MDEV-11588 Support for ONLY_FULL_GROUP_BY functional dependency
-
- Stalled
-
The first case might be considered as a bug, as GROUP BY 1 or if we name this column, then query works;
the other two cases - there is a plan to implement it in 10.6 (MDEV-11588)
MariaDB [test]> SELECT CONCAT(prefix, suffix, 'constant') as x, COUNT(*) FROM test GROUP BY x;+----------------+----------+| x | COUNT(*) |+----------------+----------+| abcdefconstant | 3 || abcxyzconstant | 1 |+----------------+----------+2 rows in set (0.006 sec)MariaDB [test]> explain extended SELECT CONCAT(prefix, suffix, 'constant') as x, COUNT(*) FROM test GROUP BY x;+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary; Using filesort |+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+1 row in set, 1 warning (0.003 sec)Note (Code 1003): select concat(`test`.`test`.`prefix`,`test`.`test`.`suffix`,'constant') AS `x`,count(0) AS `COUNT(*)` from `test`.`test` group by concat(`test`.`test`.`prefix`,`test`.`test`.`suffix`,'constant')MariaDB [test]> select concat(`test`.`test`.`prefix`,`test`.`test`.`suffix`,'constant') AS `x`,count(0) AS `COUNT(*)` from `test`.`test` group by concat(`test`.`test`.`prefix`,`test`.`test`.`suffix`,'constant');ERROR 1055 (42000): 'test.test.prefix' isn't in GROUP BY