Details
-
Task
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
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)
|