Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11588

Support for ONLY_FULL_GROUP_BY functional dependency

    XMLWordPrintable

Details

    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

          Activity

            People

              psergei Sergei Petrunia
              CodeSatori Markus A.O. Loponen
              Votes:
              17 Vote for this issue
              Watchers:
              24 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.