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
-