This is another bug where we treat the window function as an Item_sum. Window function is treated as an aggregate function and this leads to the problem here.
The query does not have any aggregate function and we still hit the problem
Here is the code where we see the error being thrown
/*
|
Check if there are references to un-aggregated columns when computing
|
aggregate functions with implicit grouping (there is no GROUP BY).
|
*/
|
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list && |
!(select_lex->master_unit()->item &&
|
select_lex->master_unit()->item->is_in_predicate() &&
|
((Item_in_subselect*)select_lex->master_unit()->item)->
|
test_set_strategy(SUBS_MAXMIN_INJECTED)) &&
|
select_lex->non_agg_field_used() &&
|
select_lex->agg_func_used())
|
{
|
my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
|
ER_THD(thd, ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
|
DBUG_RETURN(-1);
|
}
|
In the debugger I see
(lldb) p select_lex->agg_func_used()
|
(bool) $4 = true
|
(lldb) p select_lex->non_agg_field_used()
|
(bool) $5 = true
|
This means that the query has an aggregate function and atleast one field with non-aggregate function.
This does not happen in our case, we have window functions in the query, no aggregate functions.
This means that the window function is being treated as an aggregate function here.
The function select_lex->agg_func_used should return FALSE in this case
CREATE TABLE t1(a VARCHAR(10), b int); |
INSERT INTO t1 VALUES |
('Maths', 60),('Maths', 60), ('Maths', 70),('Maths', 55), ('Biology', 60), ('Biology', 70); |
SET sql_mode = 'ONLY_FULL_GROUP_BY'; |
MariaDB [test]> SELECT a, b, SUM(b) OVER (PARTITION BY a ORDER BY b) AS rank FROM t1 ORDER BY a, b DESC;
|
+---------+------+------+
|
| a | b | rank |
|
+---------+------+------+
|
| Biology | 70 | 130 |
|
| Biology | 60 | 60 |
|
| Maths | 70 | 245 |
|
| Maths | 60 | 175 |
|
| Maths | 60 | 175 |
|
| Maths | 55 | 55 |
|
+---------+------+------+
|
6 rows in set (0.003 sec)
|
|
With SUM function used as a window function, there is no problem here.
MariaDB [test]> SELECT a, b, RANK() OVER (PARTITION BY a ORDER BY b) AS rank FROM t1 ORDER BY a, b DESC;
|
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
|
With RANK function we hit the error.
Debugging in the function Item_sum::check_sum_func
For the query:
SELECT a, b, SUM(b) OVER (PARTITION BY a ORDER BY b) AS rank FROM t1 ORDER BY a, b DESC; |
(lldb) p window_func_sum_expr_flag
|
(bool) $0 = true |
and we return from this function.
For the second query
SELECT a, b, RANK() OVER (PARTITION BY a ORDER BY b) AS rank FROM t1 ORDER BY a, b DESC; |
(lldb) p window_func_sum_expr_flag
|
(bool) $1 = false
|
(lldb) p this
|
(Item_sum_rank *) $2 = 0x000062b000000648
|
Debugging till the end of the function
(lldb) p aggr_sel->agg_func_used()
|
(bool) $3 = false
|
(lldb) n
|
Process 66555 stopped
|
* thread #2, stop reason = step over
|
frame #0: 0x00000001004ff508 mysqld`Item_sum::check_sum_func(this=0x000062b000000648, thd=0x000062a00005a270, ref=0x000062b000000c38) at item_sum.cc:318:7
|
315 }
|
316 }
|
317 aggr_sel->set_agg_func_used(true);
|
-> 318 if (sum_func() == SP_AGGREGATE_FUNC)
|
319 aggr_sel->set_custom_agg_func_used(true);
|
320 update_used_tables();
|
321 thd->lex->in_sum_func= in_sum_func;
|
Target 0: (mysqld) stopped.
|
(lldb) p aggr_sel->agg_func_used()
|
(bool) $4 = true
|
We set that this is marked as an aggregate function. This is wrong, we need to make sure that this is an Item_sum instance that is used as a window function.
Patch
http://lists.askmonty.org/pipermail/commits/2020-July/014283.html
I've just come across this now.
I was wondering why was MariaDB complaining about GROUP BY clauses/columns, when I was trying to use ROW_NUMBER(), without any GROUP BY stuff.
Then I found this Issue Ticket here.
My @@sql_mode has "ONLY_FULL_GROUP_BY", as should be, and I can confirm that ROW_NUMBER() works if I disable it.
SET sql_mode = (SELECT REPLACE(REPLACE(@@sql_mode, ",ONLY_FULL_GROUP_BY", ""), "ONLY_FULL_GROUP_BY,", ""));
SELECT
ROW_NUMBER() OVER (PARTITION BY `col1` ORDER BY `col2` DESC) AS `rownum`,
...
Of course, this is not ideal...
igor Any idea when the fix can be reviewed and merged? 
Thanks!
This is something that is critical for us as well as it is causing problems - any updates?
Is this going to be fixed anytime soon? IMO this is a really critical bug which is blocking me to use basic features of the SQL.
The second patch is ok. I've edited the commit comment to have more information.
bb-10.2-mdev17785