[MDEV-15837] Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed in compare_order_elements function Created: 2018-04-10  Updated: 2019-05-24  Resolved: 2019-05-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.2.24, 10.3.15, 10.4.5

Type: Bug Priority: Critical
Reporter: Alice Sherepa Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-19456 Server crashes using ROW_NUMBER() in ... Closed
is duplicated by MDEV-19583 [ERROR] mysqld got signal 11 Closed
Relates
relates to MDEV-9897 Window functions: crash when ORDER BY... Closed
relates to MDEV-9896 Testing for window functions Open
relates to MDEV-18015 Assertion `global_status_var.global_m... Closed
relates to MDEV-18373 DENSE_RANK is not calculated correctly Closed
relates to MDEV-19364 Server crashes on certain window func... Closed
relates to MDEV-19398 Assertion `item1->type() == Item::FI... Closed

 Description    

--source include/have_innodb.inc
 
CREATE TABLE t (a1 int) engine=innodb ;
 
 
 
SELECT  BIT_OR(`a1`) OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t;


mysqld: /home/alice/git/10.3/sql/sql_window.cc:421: int compare_order_elements(ORDER*, ORDER*): Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed.
 
180410 13:50:38 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.3.6-MariaDB-debug-log
 
key_buffer_size=1048576
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=7
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63321 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x62a000090208
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f60d2cdbdf0 thread_stack 0x49000
 
/usr/lib/x86_64-linux-gnu/libasan.so.2(+0x4a077)[0x7f60e9e4c077]
 
mysys/stacktrace.c:269(my_print_stacktrace)[0x5584dac89dc0]
 
sql/signal_handler.cc:168(handle_fatal_signal)[0x5584d9bcc88b]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f60e939e390]
 
linux/raise.c:54(__GI_raise)[0x7f60e8757428]
 
stdlib/abort.c:91(__GI_abort)[0x7f60e875902a]
 
assert/assert.c:92(__assert_fail_base)[0x7f60e874fbd7]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7f60e874fc82]
 
sql/sql_window.cc:422(compare_order_elements(st_order*, st_order*))[0x5584d998fcc9]
 
sql/sql_window.cc:445(compare_order_lists(SQL_I_List<st_order>*, SQL_I_List<st_order>*))[0x5584d998ffb8]
 
sql/sql_window.cc:571(compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*))[0x5584d9990973]
 
sql/sql_list.h:581(void bubble_sort<Item_window_func>(List<Item_window_func>*, int (*)(Item_window_func*, Item_window_func*, void*), void*))[0x5584d999dd42]
 
sql/sql_window.cc:645(order_window_funcs_by_window_specs(List<Item_window_func>*))[0x5584d9990f0e]
 
sql/sql_window.cc:3028(Window_funcs_computation::setup(THD*, List<Item_window_func>*, st_join_table*))[0x5584d9995794]
 
sql/sql_select.cc:3252(JOIN::make_aggr_tables_info())[0x5584d95bc72d]
 
sql/sql_select.cc:2577(JOIN::optimize_stage2())[0x5584d95b596e]
 
sql/sql_select.cc:1895(JOIN::optimize_inner())[0x5584d95aefda]
 
sql/sql_select.cc:1467(JOIN::optimize())[0x5584d95ab172]
 
sql/sql_select.cc:4174(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*))[0x5584d95c4bdb]
 
sql/sql_select.cc:382(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5584d95a04be]
 
sql/sql_parse.cc:6556(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5584d9526e28]
 
sql/sql_parse.cc:3770(mysql_execute_command(THD*))[0x5584d95152fe]
 
sql/sql_parse.cc:8013(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5584d952eed8]
 
sql/sql_parse.cc:1844(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5584d9509e1a]
 
sql/sql_parse.cc:1387(do_command(THD*))[0x5584d9506fc1]
 
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x5584d9854cc8]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x5584d98546d0]
 
perfschema/pfs.cc:1864(pfs_spawn_thread)[0x5584da1169a9]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f60e93946ba]
 
x86_64/clone.S:111(clone)[0x7f60e882941d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x61100007d2e0): SELECT  BIT_OR(`a1`) OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t
 
Connection ID (thread ID): 9
 
Status: NOT_KILLED



 Comments   
Comment by Alice Sherepa [ 2018-04-20 ]

another kind, that looks very alike

Thread pointer: 0x7fb0d00009a8
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fb138b21e98 thread_stack 0x49000
 
mysys/stacktrace.c:270(my_print_stacktrace)[0x55bd98c41809]
 
sql/signal_handler.cc:168(handle_fatal_signal)[0x55bd98790fa7]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fb13be7f390]
 
sql/sql_window.cc:423(compare_order_elements)[0x55bd986f70f2]
 
sql/sql_window.h:146(Window_spec::disjoin_partition_and_order_lists())[0x55bd986f72cf]
 
sql/sql_window.cc:606(compare_window_funcs_by_window_specs)[0x55bd986fa5bb]
 
sql/sql_select.cc:3216(JOIN::make_aggr_tables_info())[0x55bd9861ab26]
 
sql/sql_select.cc:2541(JOIN::optimize_stage2())[0x55bd98621a3e]
 
sql/sql_select.cc:1511(JOIN::optimize_inner())[0x55bd9862320d]
 
sql/sql_select.cc:1433(JOIN::optimize())[0x55bd9862340f]
 
sql/sql_select.cc:4138(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*))[0x55bd986252e1]
 
sql/sql_select.cc:382(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55bd98625417]
 
sql/sql_parse.cc:6541(execute_sqlcom_select(THD*, TABLE_LIST*) [clone .constprop.235])[0x55bd984fd25e]
 
sql/sql_parse.cc:3768(mysql_execute_command(THD*))[0x55bd985da116]
 
sql/sql_parse.cc:8001(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55bd985dc22d]
 
sql/sql_parse.cc:1846(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55bd985dec02]
 
sql/sql_parse.cc:1393(do_command(THD*))[0x55bd985df486]
 
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x55bd986a0734]
 
sql/sql_connect.cc:1310(handle_one_connection)[0x55bd986a08a4]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fb13be756ba]
 
x86_64/clone.S:111(clone)[0x7fb13b30a41d]

Comment by Varun Gupta (Inactive) [ 2018-04-25 ]

So for this case we have items of different types inside the query, so for the first window function we have order by a constant while for the other we have order by a function

(gdb) p item2->type()
 
$2 = Item::FUNC_ITEM
 
(gdb) p item1->type()
 
$3 = Item::INT_ITEM
 
(gdb) p (char*)dbug_print_item(item1)
 
$4 = 0x5555573c6180 <dbug_item_print_buf> "1"
 
(gdb) p (char*)dbug_print_item(item2)
 
$6 = 0x5555573c6180 <dbug_item_print_buf> "export_set(5,'Y','N',',',4)"

Comment by Varun Gupta (Inactive) [ 2018-04-25 ]

For a query like

SELECT BIT_OR(`a1`) OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(a1,'Y','N',',',4))) FROM t;
note that the second window function has a field a1 attached from the table t in the order by clause, so for this case we have

(gdb) p item2->type()       
$9 = Item::FIELD_ITEM
 
(gdb) p (char*)dbug_print_item(item2)
 
$10 = 0x5555573c6180 <dbug_item_print_buf> "tmp_field"
 
(gdb) p (char*)dbug_print_item(item1)
 
$11 = 0x5555573c6180 <dbug_item_print_buf> "1"
 
(gdb) p item1->type()
 
$12 = Item::INT_ITEM

Comment by Varun Gupta (Inactive) [ 2018-04-25 ]

A solution for this issue is to ignore all constants and only compare the field items in the order by clauses of multiple window functions.

Comment by Alice Sherepa [ 2018-04-25 ]

nearly the same test, but second window function is inside ORDER BY clause. (signal 11 in this case)

--source include/have_innodb.inc
 
 
 
CREATE TABLE `X1` (`f1` int,`f3` int) engine=innodb;
 
 
 
SELECT CUME_DIST() OVER ( ORDER BY f1 ) FROM X1
 
ORDER BY LEAD(f3) OVER ( ORDER BY (PI()));
 
drop table X1;


Thread 1 (Thread 0x7f46c5cf3700 (LWP 6298)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x000055ebe3fdbc5a in my_write_core (sig=sig@entry=11) at /home/alice/git/10.3/mysys/stacktrace.c:481
 
#2  0x000055ebe3b2b7b4 in handle_fatal_signal (sig=11) at /home/alice/git/10.3/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x000055ebe3a91852 in compare_order_elements (ord1=0x7f46740119f8, ord1=0x7f46740119f8, ord2=0x7f4674012640, ord2=0x7f4674012640) at /home/alice/git/10.3/sql/sql_window.cc:423
 
#5  compare_order_lists (part_list1=<optimized out>, part_list2=<optimized out>) at /home/alice/git/10.3/sql/sql_window.cc:445
 
#6  0x000055ebe3a94c74 in compare_window_funcs_by_window_specs (arg=0x0, win_func2=0x7f4674012710, win_func1=0x7f4674011ac8) at /home/alice/git/10.3/sql/sql_window.cc:571
 
#7  bubble_sort<Item_window_func> (arg=<optimized out>, sort_func=<optimized out>, list_to_sort=<optimized out>) at /home/alice/git/10.3/sql/sql_list.h:581
 
#8  order_window_funcs_by_window_specs (win_func_list=0x7f4674005178) at /home/alice/git/10.3/sql/sql_window.cc:643
 
#9  Window_funcs_computation::setup (this=0x7f4674014d28, thd=0x7f46740009a8, window_funcs=0x7f4674005178, tab=tab@entry=0x7f4674013ef8) at /home/alice/git/10.3/sql/sql_window.cc:3026
 
#10 0x000055ebe39b5816 in JOIN::make_aggr_tables_info (this=this@entry=0x7f4674012928) at /home/alice/git/10.3/sql/sql_select.cc:3218
 
#11 0x000055ebe39bc72e in JOIN::optimize_stage2 (this=this@entry=0x7f4674012928) at /home/alice/git/10.3/sql/sql_select.cc:2543
 
#12 0x000055ebe39bdf0d in JOIN::optimize_inner (this=this@entry=0x7f4674012928) at /home/alice/git/10.3/sql/sql_select.cc:1861
 
#13 0x000055ebe39be10f in JOIN::optimize (this=this@entry=0x7f4674012928) at /home/alice/git/10.3/sql/sql_select.cc:1431
 
#14 0x000055ebe39bffe1 in mysql_select (thd=thd@entry=0x7f46740009a8, tables=0x7f4674011c58, wild_num=<optimized out>, fields=..., conds=0x0, og_num=1, order=0x7f46740127f0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f4674012908, unit=0x7f4674004628, select_lex=0x7f4674004da0) at /home/alice/git/10.3/sql/sql_select.cc:4140
 
#15 0x000055ebe39c0117 in handle_select (thd=thd@entry=0x7f46740009a8, lex=lex@entry=0x7f4674004560, result=result@entry=0x7f4674012908, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/alice/git/10.3/sql/sql_select.cc:382
 
#16 0x000055ebe3897d0e in execute_sqlcom_select (thd=thd@entry=0x7f46740009a8, all_tables=0x7f4674011c58) at /home/alice/git/10.3/sql/sql_parse.cc:6539
 
#17 0x000055ebe3974dd6 in mysql_execute_command (thd=thd@entry=0x7f46740009a8) at /home/alice/git/10.3/sql/sql_parse.cc:3768
 
#18 0x000055ebe3976eed in mysql_parse (thd=0x7f46740009a8, rawbuf=<optimized out>, length=89, parser_state=0x7f46c5cf2610, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /home/alice/git/10.3/sql/sql_parse.cc:8001
 
#19 0x000055ebe39798d2 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f46740009a8, packet=packet@entry=0x7f4674009319 "SELECT CUME_DIST() OVER ( ORDER BY f1 ) FROM X1\nORDER BY LEAD(f3) OVER ( ORDER BY (PI()))", packet_length=packet_length@entry=89, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /home/alice/git/10.3/sql/sql_parse.cc:1846
 
#20 0x000055ebe397a156 in do_command (thd=0x7f46740009a8) at /home/alice/git/10.3/sql/sql_parse.cc:1391
 
#21 0x000055ebe3a3afb4 in do_handle_one_connection (connect=connect@entry=0x55ebe6c56b98) at /home/alice/git/10.3/sql/sql_connect.cc:1402
 
#22 0x000055ebe3a3b124 in handle_one_connection (arg=arg@entry=0x55ebe6c56b98) at /home/alice/git/10.3/sql/sql_connect.cc:1308
 
#23 0x000055ebe3ca6ad4 in pfs_spawn_thread (arg=0x55ebe6bef008) at /home/alice/git/10.3/storage/perfschema/pfs.cc:1862
 
#24 0x00007f46cc4b56ba in start_thread (arg=0x7f46c5cf3700) at pthread_create.c:333
 
#25 0x00007f46cb94a41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Varun Gupta (Inactive) [ 2018-04-25 ]

When i run the query in MYSQL then i get this error

SELECT  rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY 1) FROM t' failed: 3592: Window '<unnamed window>': ORDER BY or PARTITION BY uses legacy position indication which is not supported, use expression

Comment by Varun Gupta (Inactive) [ 2018-04-25 ]

Patch for the issue
http://lists.askmonty.org/pipermail/commits/2018-April/012458.html

Comment by Alice Sherepa [ 2019-03-27 ] 

create table t1 (a int, b int);
 
insert into t1 values (1,3), (2,2), (3,1), (1,3), (2,2), (3,1);
 
select  lag(a) over (order by null desc), sum(a) over (order by null) from t1;

Comment by Alice Sherepa [ 2019-05-01 ]

with ASAN (10.2-10.4) : Assertion `global_status_var.global_memory_used == 0' failed:

10.2 810f014ca7a705381e110cb266

 
create table t1 (a int, b int);
 
insert into t1 values (1,3), (2,2), (3,1), (1,3), (2,2), (3,1);
 
select  lag(a) over (order by null desc), sum(a) over (order by null) from t1;
 
lag(a) over (order by null desc)	sum(a) over (order by null)
 
NULL	12
 
2	12
 
3	12
 
1	12
 
2	12
 
3	12
 
***Warnings generated in error logs during shutdown after running tests: main.1_my
 
 
 
mysqld: /home/alice/git/10.2/sql/mysqld.cc:2183: void mysqld_exit(int): Assertion `global_status_var.global_memory_used == 0' failed.

Comment by Varun Gupta (Inactive) [ 2019-05-01 ]

The above issue is already encountered in MDEV-18015, not directly related to this issue

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