Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
-
None
Description
CREATE TABLE t1 ( a char(25), b text); |
INSERT INTO t1 VALUES ('foo','bar'); |
|
|
SELECT SUM(b) OVER (PARTITION BY a), ROW_NUMBER() OVER (PARTITION BY b) |
FROM t1 |
GROUP BY LEFT((SYSDATE()), 'foo') WITH ROLLUP; |
|
10.2 54d0a55adf6fbfc92c5473b |
mysqld: /10.2/sql/sql_window.cc:322: int compare_order_elements(ORDER*, ORDER*): Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed.
|
190506 10:48:00 [ERROR] mysqld got signal 6 ;
|
|
|
linux/raise.c:54(__GI_raise)[0x7f70751d2428]
|
stdlib/abort.c:91(__GI_abort)[0x7f70751d402a]
|
assert/assert.c:92(__assert_fail_base)[0x7f70751cabd7]
|
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7f70751cac82]
|
sql/sql_window.cc:323(compare_order_elements(st_order*, st_order*))[0x558ae3b19362]
|
sql/sql_window.cc:361(compare_order_lists(SQL_I_List<st_order>*, SQL_I_List<st_order>*))[0x558ae3b19924]
|
sql/sql_window.cc:474(compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*))[0x558ae3b1a0b5]
|
sql/sql_list.h:626(void bubble_sort<Item_window_func>(List<Item_window_func>*, int (*)(Item_window_func*, Item_window_func*, void*), void*))[0x558ae3b2719b]
|
sql/sql_window.cc:561(order_window_funcs_by_window_specs(List<Item_window_func>*))[0x558ae3b1a830]
|
sql/sql_window.cc:2897(Window_funcs_computation::setup(THD*, List<Item_window_func>*, st_join_table*))[0x558ae3b1ed34]
|
sql/sql_select.cc:2914(JOIN::make_aggr_tables_info())[0x558ae379498c]
|
sql/sql_select.cc:2233(JOIN::optimize_inner())[0x558ae378dd41]
|
sql/sql_select.cc:1116(JOIN::optimize())[0x558ae3783103]
|
sql/sql_select.cc:3806(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*))[0x558ae379c66b]
|
sql/sql_select.cc:376(handle_select(THD*, LEX*, select_result*, unsigned long))[0x558ae377bf4b]
|
sql/sql_parse.cc:6486(execute_sqlcom_select(THD*, TABLE_LIST*))[0x558ae37039e7]
|
sql/sql_parse.cc:3534(mysql_execute_command(THD*))[0x558ae36f0ddc]
|
sql/sql_parse.cc:8020(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x558ae370c14b]
|
sql/sql_parse.cc:1835(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x558ae36e77d5]
|
sql/sql_parse.cc:1387(do_command(THD*))[0x558ae36e4952]
|
sql/sql_connect.cc:1335(do_handle_one_connection(CONNECT*))[0x558ae3a0e146]
|
sql/sql_connect.cc:1242(handle_one_connection)[0x558ae3a0db4e]
|
perfschema/pfs.cc:1864(pfs_spawn_thread)[0x558ae4bbc1c8]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f7075e0f6ba]
|
x86_64/clone.S:111(clone)[0x7f70752a441d]
|
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
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.
test case without using WITH ROLLUP: