[MDEV-25133] sql mode ONLY_FULL_GROUP_BY lets query fail when grouping expression contains constant Created: 2021-03-14  Updated: 2021-04-05

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5.9
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Remy Fox Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: only_full_group_by

Issue Links:
Relates
relates to MDEV-11588 Support for ONLY_FULL_GROUP_BY functi... Stalled

 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);



 Comments   
Comment by Alice Sherepa [ 2021-03-15 ]

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

Generated at Thu Feb 08 09:35:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.