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

Expressions should be properly supported in GROUP BY in ONLY_FULL_GROUP_BY sql_mode

    XMLWordPrintable

Details

    Description

      As MySQL (and MariaDB) support expressions in GROUP BY clause (see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html), it would be useful to support them in ONLY_FULL_GROUP_BY mode as well (taking the mode details into account).

      Now (in 10.2) it seems even obviously logically correct queries are rejected, while in even more trivial cases expressions in GROUP BY work. Consider the following example:

      MariaDB [test]> create table tt(id int primary key, a int, b int);
      Query OK, 0 rows affected (0.28 sec)
       
      MariaDB [test]> select @@sql_mode;
      +-------------------------------------------------------------------------------------------+
      | @@sql_mode                                                                                |
      +-------------------------------------------------------------------------------------------+
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a,coalesce(b,1)*a from tt group by a,coalesce(b,1);
      Empty set (0.00 sec)
       
      MariaDB [test]> set sql_mode=only_full_group_by;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> select a,coalesce(b,1)*a from tt group by a,coalesce(b,1);
      ERROR 1055 (42000): 'test.tt.b' isn't in GROUP BY
      MariaDB [test]> select a, floor(b) from tt group by a, floor(b);
      Empty set (0.01 sec)
       
      MariaDB [test]> select a, floor(b)*2 from tt group by a, floor(b);
      ERROR 1055 (42000): 'test.tt.b' isn't in GROUP BY
       
      MariaDB [test]> select a, mod(b,2) from tt group by a, mod(b,2);
      Empty set (0.00 sec)
       
      MariaDB [test]> select a, 2*mod(b,2) from tt group by a, mod(b,2);
      ERROR 1055 (42000): 'test.tt.b' isn't in GROUP BY
      ...
      

      So, either we should reject all kinds of expressions in GROUP_BY when we use ONLY_FULL_GROUP_BY mode, or we should take functional dependencies on these expressions into account properly.

      This is probably related to https://jira.mariadb.org/browse/MDEV-11588

      See also upstream https://bugs.mysql.com/bug.php?id=86475

      Attachments

        Activity

          People

            Unassigned Unassigned
            valerii Valerii Kravchuk
            Votes:
            1 Vote for this issue
            Watchers:
            2 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.