Details
-
New Feature
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
Description
When SQL mode ONLY_FULL_GROUP_BY is enabled (as is default), MariaDB rejects, per the '92 SQL standard, any SELECT queries with (non-aggregated) columns not also listed in the GROUP BY statement with error 1055: "'db.table.column_name' isn't in GROUP_BY".
However, the 2003 spec loosens this restriction with a requirement that the columns need only be functionally dependent:
"17) If T is a grouped table, then let G be the set of grouping columns of T. In each ((value expression)) contained in ((select list)) , each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a ((set function specification)) whose aggregation query is QS."
Since version 5.7.5, available since late 2014, MySQL supports the detection of functional dependencies in GROUP BY statements. It would be good to see this ported over to MariaDB as well to eliminate the need for redundant double column listing (and presumable performance hit when dependent columns are needlessly iterated for grouping), where functional dependency could be detected. For some useful background, see this entry at the MySQLServerTeam blog, along with Roland Bouman's related write-up .
Since ONLY_FULL_GROUP_BY is now a default both in Maria and MySQL, not porting this to Maria would make migration from MySQL to Maria more involved, forcing either SQL mode or code changes where affected queries are used. (There's a related older ticket mentioning the ANY_VALUE() function to deal with the over-strict mode, however that alone makes for a clunky work-around to over-strict grouping.)
Attachments
Issue Links
- blocks
-
MDEV-28109 ANSI Mode & ONLY_FULL_GROUP_BY
- Open
- includes
-
MDEV-12867 Full scan despite appropriate index
- Stalled
-
MDEV-19307 Use functional dependencies when searching for conditions that can be pushed into materialized derived tables
- Open
-
MDEV-20541 Implement a flag to show if an expression is non-deterministic
- Stalled
- is duplicated by
-
MDEV-24239 ONLY_FULL_GROUP_BY not restricting ORDER BY
- Closed
- relates to
-
MDEV-20409 With ONLY_FULL_GROUP_BY work don't correct.
- Closed
-
MDEV-25133 sql mode ONLY_FULL_GROUP_BY lets query fail when grouping expression contains constant
- Open