|
test case without using WITH ROLLUP:
CREATE TABLE t1 (a int);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
set @x:=2;
|
SELECT PERCENT_RANK() OVER (ORDER BY (@x:=a)), SUM(a) OVER (ORDER BY a) FROM t1 GROUP BY a ;
|
|
|
Patch
http://lists.askmonty.org/pipermail/commits/2019-August/013928.html
|
|
CREATE TABLE t1 ( id varchar(10));
|
INSERT INTO t1 values (1),(2),(3);
|
|
SELECT dense_rank() over (ORDER BY avg(1)), rank() over (ORDER BY avg(1)) FROM t1 GROUP BY nullif(id, 15532);
|
I used the above patch, but getting the assertion:
mariadbd: /git/10.5/sql/sql_window.cc:437: int compare_order_elements(ORDER*, ORDER*): Assertion `fld1 && fld2' failed.
|
201207 16:54:24 [ERROR] mysqld got signal 6 ;
|
|
Server version: 10.5.9-MariaDB-debug-log
|
|
:0(__GI___assert_fail)[0x7f59a0794f36]
|
sql/sql_window.cc:438(compare_order_elements(st_order*, st_order*))[0x561a81d3435c]
|
sql/sql_window.cc:476(compare_order_lists(SQL_I_List<st_order>*, SQL_I_List<st_order>*))[0x561a81d34512]
|
sql/sql_window.cc:601(compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*))[0x561a81d3494e]
|
sql/sql_list.h:655(void bubble_sort<Item_window_func>(List<Item_window_func>*, int (*)(Item_window_func*, Item_window_func*, void*), void*))[0x561a81d3afba]
|
sql/sql_window.cc:676(order_window_funcs_by_window_specs(List<Item_window_func>*))[0x561a81d34afc]
|
sql/sql_window.cc:3094(Window_funcs_computation::setup(THD*, List<Item_window_func>*, st_join_table*))[0x561a81d36f3b]
|
sql/sql_select.cc:3708(JOIN::make_aggr_tables_info())[0x561a81b122b0]
|
sql/sql_select.cc:2992(JOIN::optimize_stage2())[0x561a81b0f58d]
|
sql/sql_select.cc:2277(JOIN::optimize_inner())[0x561a81b0cd72]
|
sql/sql_select.cc:1627(JOIN::optimize())[0x561a81b0a7c5]
|
sql/sql_select.cc:4652(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x561a81b15a4c]
|
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x561a81b056ef]
|
sql/sql_parse.cc:6264(execute_sqlcom_select(THD*, TABLE_LIST*))[0x561a81ac80df]
|
sql/sql_parse.cc:3968(mysql_execute_command(THD*))[0x561a81abf1b9]
|
sql/sql_parse.cc:8042(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x561a81accf6c]
|
sql/sql_parse.cc:1875(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x561a81ab8f4f]
|
sql/sql_parse.cc:1353(do_command(THD*))[0x561a81ab7743]
|
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x561a81c6507f]
|
sql/sql_connect.cc:1314(handle_one_connection)[0x561a81c64de2]
|
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x561a821c1db9]
|
nptl/pthread_create.c:478(start_thread)[0x7f59a0cac609]
|
x86_64/clone.S:97(__GI___clone)[0x7f59a0880293]
|
|
Query (0x7f5968013f30): SELECT dense_rank() over (ORDER BY avg(1)), rank() over (ORDER BY avg(1)) FROM t1 GROUP BY nullif(id, 15532)
|
|
|
The patch earlier fixed the crash but the results were not correct. With ROLLUP the results were not correct.
|
|
test derived from MDEV-28081:
SELECT AVG (1) OVER (PARTITION BY 'x'/17) / AVG (1) OVER (PARTITION BY AVG(1)) ;
|
mysqld: /10.2/src/sql/sql_window.cc:321: int compare_order_elements(ORDER*, ORDER*): Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed.
|
220318 14:47:39 [ERROR] mysqld got signal 6 ;
|
|
Server version: 10.2.44-MariaDB-debug-log
|
|
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb)[0x7f911925b03b]
|
/lib/x86_64-linux-gnu/libc.so.6(abort+0x12b)[0x7f911923a859]
|
sql/sql_window.cc:323(compare_order_elements(st_order*, st_order*))[0x56458a52bfb9]
|
sql/sql_window.cc:361(compare_order_lists(SQL_I_List<st_order>*, SQL_I_List<st_order>*))[0x56458a52c581]
|
sql/sql_window.cc:473(compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*))[0x56458a52cd29]
|
sql/sql_list.h:639(void bubble_sort<Item_window_func>(List<Item_window_func>*, int (*)(Item_window_func*, Item_window_func*, void*), void*))[0x56458a53a814]
|
sql/sql_window.cc:573(order_window_funcs_by_window_specs(List<Item_window_func>*))[0x56458a52d5a1]
|
sql/sql_window.cc:2910(Window_funcs_computation::setup(THD*, List<Item_window_func>*, st_join_table*))[0x56458a531cc2]
|
sql/sql_select.cc:2940(JOIN::make_aggr_tables_info())[0x56458a138038]
|
sql/sql_select.cc:2295(JOIN::optimize_inner())[0x56458a130c09]
|
sql/sql_select.cc:1127(JOIN::optimize())[0x56458a124eb6]
|
sql/sql_select.cc:3835(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56458a1405bd]
|
sql/sql_select.cc:361(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56458a11d184]
|
sql/sql_parse.cc:6271(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56458a0937ef]
|
sql/sql_parse.cc:3582(mysql_execute_command(THD*))[0x56458a080b78]
|
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56458a09cd04]
|
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56458a075f09]
|
sql/sql_parse.cc:1381(do_command(THD*))[0x56458a072cd4]
|
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x56458a3ff40a]
|
sql/sql_connect.cc:1242(handle_one_connection)[0x56458a3feccd]
|
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56458b79faf4]
|
nptl/pthread_create.c:478(start_thread)[0x7f9119bc9609]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f9119337163]
|
|
Query (0x62b000000290): SELECT AVG (1) OVER (PARTITION BY 'x'/17) / AVG (1) OVER (PARTITION BY AVG(1))
|
|
|
Investigating...
Ok the issue is with this window function:
ROW_NUMBER() OVER (PARTITION BY b)
|
compare_order_elements() expects the ORDER lists to contain Item_field objects, while
that function has an Item_copy_string.
The Item_copy_string is created by setup_copy_fields(). It is created to handle blob columns (that's why we have a problem with PARTITION BY b and not with PARTITION BY a), as well as some other kinds of Items (in Alice's testcase it is Item_func_div representing 'x'/17)
Then, find_order_in_list() puts it into Window_spec's ORDER structure.
First idea: if compare_order_elements() needs a field number, let's reach into Item_copy's underlying item and get it.
This is what Varun's patch does. It implements Item_copy::get_tmp_table_field():
+ Field *get_tmp_table_field()
|
+ {
|
+ return item->get_tmp_table_field();
|
+ }
|
and makes compare_order_elements call item1->get_tmp_table_field().
My concerns with this back when this was coded were: what exactly is get_tmp_table_field() and what are the consequences of implementing it...
|
|
In the first of Alice's testcases
https://jira.mariadb.org/browse/MDEV-19398?focusedCommentId=174396&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-174396
compare_order_elements() tries to compare Item_avg_field_decimal objects. These items are not covered by Varun's patch so the testcase still crashes.
|
|
Note that this is not limited to Item_copy or Item_sum_field-derived items.
Slightly modified Alice's testcase:
CREATE TABLE t1 ( id varchar(10));
|
INSERT INTO t1 values (1),(2),(3);
|
|
SELECT
|
dense_rank() over (ORDER BY avg(1)+3),
|
rank() over (ORDER BY avg(1))
|
FROM t4
|
GROUP BY
|
nullif(id, 15532);
|
This crashes compare_order_elements, too, and we have:
(gdb) p item1
|
$8 = (Item_func_plus *) 0x7fff74013ad8
|
(gdb) p item2
|
$9 = (Item_avg_field_decimal *) 0x7fff740178d0
|
... so the ORDER BY list can have an arbitrary item.
I don't see this causing any problems in window function computation code btw. Only compare_order_elements has an issue.
|
|
In the above case (avg(1) + 3 is not stored as a field in any temporary table.
|
|
Suggestion: can we get rid of the crash by adjusting the comparison function to
- compare Item_field objects like it does now
- any item that's not an Item_field is greater than any Item_field
- two non-Item-field items are compared as their pointers.
This is "compatible" with the current ordering for Item_field objects.
It may miss some optimization opportunities when multiple window functions use the same PARTITION/ORDER BY expressions.
Comparing Item* pointers means the ordering will vary across executions. (What is the effect of this? Window Function step will run sorts in different order?)
|
|
... yes, implementing the above will have an issue that window_functions_computation.sorts will list the sorts in arbitrary order:
{
|
"query_block": {
|
"select_id": 1,
|
"filesort": {
|
"sort_key": "nullif(t1.`id`,15532)",
|
"window_functions_computation": {
|
"sorts": {
|
"filesort": {
|
"sort_key": "avg(1) + 3"
|
},
|
"filesort": {
|
"sort_key": "avg(1)"
|
}
|
},
|
"temporary_table": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 3,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
|
|
Ok, the new fix is here https://github.com/MariaDB/server/commit/85cc56875e9ddb7f5f56013bac22e36805288c16
and it fixes MDEV-26614 , too.
|