[MDEV-10672] Implement sql_mode ONLY_FULL_GROUP_BY for windowing functions Created: 2016-08-26  Updated: 2016-10-31

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Fix Version/s: None

Type: Task Priority: Major
Reporter: Valerii Kravchuk Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

It is possible to write windowing functions on non-aggregate if there is a group by. That should probably be forbidden by ONLY_FULL_GROUP_BY sql mode, as the result is non useful for the user (because the windows are calculated after the group by).

Check this simple case:

MariaDB [test]> create table t(a int,b int, c int , d int);
 
Query OK, 0 rows affected (0.27 sec)
 
 
 
MariaDB [test]> insert into t(a,b,c,d) values(1, rand()*1000, rand()*1000, rand()*1000);
 
Query OK, 1 row affected (0.08 sec)
 
 
 
MariaDB [test]> insert into t(a,b,c,d) values(1, rand()*1000, rand()*1000, rand()*1000);
 
Query OK, 1 row affected (0.04 sec)
 
 
 
MariaDB [test]> replace into t(a,b,c,d) select 1, rand()*1000, rand()*1000, rand()*1000 from t t1, t t2, t t3, t t4;
 
Query OK, 16 rows affected (0.05 sec)
 
Records: 16  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select @@sql_mode;
 
+--------------------------------------------+
 
| @@sql_mode                                 |
 
+--------------------------------------------+
 
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 
+--------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> set session sql_mode=ONLY_FULL_GROUP_BY;
 
Query OK, 0 rows affected (0.02 sec)
 
 
 
MariaDB [test]> select @@sql_mode;
 
+--------------------+
 
| @@sql_mode         |
 
+--------------------+
 
| ONLY_FULL_GROUP_BY |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select a,b,c, sum(d) over(partition by a,b,c) as s from t group by a,b,c;
 
+------+------+------+------+
 
| a    | b    | c    | s    |
 
+------+------+------+------+
 
|    1 |   33 |  685 |  327 |
 
|    1 |   50 |  317 |  433 |
 
|    1 |  119 |  262 |  953 |
 
|    1 |  131 |  368 |  444 |
 
|    1 |  179 |  115 |   40 |
 
|    1 |  234 |  973 |  161 |
 
|    1 |  260 |  611 |  275 |
 
|    1 |  378 |  406 |  896 |
 
|    1 |  384 |  450 |   96 |
 
|    1 |  545 |  898 |  856 |
 
|    1 |  573 |  616 |  358 |
 
|    1 |  579 |  912 |  824 |
 
|    1 |  587 |  365 |   66 |
 
|    1 |  843 |  168 |  311 |
 
|    1 |  853 |  144 |  162 |
 
|    1 |  886 |  948 |   84 |
 
|    1 |  942 |  635 |  350 |
 
|    1 |  980 |   40 |  260 |
 
+------+------+------+------+
 
18 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select a,b,c, sum(d) as s from t group by a,b,c;               
+------+------+------+------+
 
| a    | b    | c    | s    |
 
+------+------+------+------+
 
|    1 |   33 |  685 |  327 |
 
|    1 |   50 |  317 |  433 |
 
|    1 |  119 |  262 |  953 |
 
|    1 |  131 |  368 |  444 |
 
|    1 |  179 |  115 |   40 |
 
|    1 |  234 |  973 |  161 |
 
|    1 |  260 |  611 |  275 |
 
|    1 |  378 |  406 |  896 |
 
|    1 |  384 |  450 |   96 |
 
|    1 |  545 |  898 |  856 |
 
|    1 |  573 |  616 |  358 |
 
|    1 |  579 |  912 |  824 |
 
|    1 |  587 |  365 |   66 |
 
|    1 |  843 |  168 |  311 |
 
|    1 |  853 |  144 |  162 |
 
|    1 |  886 |  948 |   84 |
 
|    1 |  942 |  635 |  350 |
 
|    1 |  980 |   40 |  260 |
 
+------+------+------+------+
 
18 rows in set (0.00 sec)



 Comments   
Comment by Valerii Kravchuk [ 2016-08-27 ]

This is how Oracle 11g threats the same statement:

root@a2f69647f46d:~# sqlplus system/oracle
 
 
 
SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 27 15:02:26 2016
 
 
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
 
 
 
Connected to:
 
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
 
 
SQL> create table t(a number, b number, c number, d number);
 
 
 
Table created.
 
 
 
SQL> insert into t values (1, 1, 1, 1);
 
 
 
1 row created.
 
 
 
SQL> insert into t values (1, 1, 1, 2);
 
 
 
1 row created.
 
 
 
SQL> insert into t values (2, 1, 1, 1);
 
 
 
1 row created.
 
 
 
SQL> select a,b,c, sum(d) over(partition by a,b,c) as s from t group by a,b,c;
 
select a,b,c, sum(d) over(partition by a,b,c) as s from t group by a,b,c
 
                  *
 
ERROR at line 1:
 
ORA-00979: not a GROUP BY expression
 
 
 
 
 
SQL> select a, b, c, sum(d) from t group by a, b;
 
select a, b, c, sum(d) from t group by a, b
 
             *
 
ERROR at line 1:
 
ORA-00979: not a GROUP BY expression
 
 
 
 
 
SQL> select a,b,c, sum(d) over(partition by a,b,c) as s from t;
 
 
 
         A          B          C          S
 
---------- ---------- ---------- ----------
 
         1          1          1          3
 
         1          1          1          3
 
         2          1          1          1
 
 
 
SQL>

So, Oracle refuses it with the same error as the case of non-aggregated column mentioned in SELECT list that is not in the GROUP BY list.

I'd say that current behavior is buggy and request to apply ONLY_FULL_GROUP_BY sql_mode here is reasonable.

Generated at Thu Feb 08 07:44:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.