[MDEV-16736] crash on lag() over() and order not working - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.2.14, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: N/A
Component/s: Optimizer - Window functions
Labels:
None
Environment:
OpenSuse Leap 15

Description

MariaDB server crashes. To reproduce this error, please execute the following script:

create or replace database BugDB;

use BugDB;

create or replace table lead_lag_order_bug (

	id int(11) NOT NULL AUTO_INCREMENT,

	TnNr int(11) not null,

	Victorys int(11) not null default 0,

	Points int(11) not null default 0,

	Dv64 bit(64) default b'0',	/*indicate witch TnNr (BitNr is TnNr) has beaten (Direct comparison)*/

	PRIMARY KEY (`id`)

insert into lead_lag_order_bug(TnNr,Victorys,Points,Dv64) values

(1,2,10,b'0'),

(2,2,10,b'1'),

(3,3,20,b'0'),

(4,3,20,b'100')

/*bug number one -> order not working*/

select bin(Dv64) bitSet,

	lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc, TnNR) as DvTnNr,

	TnNr,

	Victorys,

	Points,

	(Dv64 >> (lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc,TnNr)-1)) & 1 as Dv

from lead_lag_order_bug t

order by Victorys desc, Points desc, Dv desc, TnNr

/*bug number two -> segmentation fault*/

select bin(Dv64) bitSet,

	lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc, TnNR) as DvTnNr,

	TnNr,

	Victorys,

	Points,

	(Dv64 >> (lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc,TnNr)-1)) & 1 as Dv

from lead_lag_order_bug t

order by

	Victorys desc,

	Points desc,

	((Dv64 >> (lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc,TnNr)-1)) & 1) desc,

	TnNr

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mysqld.log
8 kB
2018-07-12 09:21

Issue Links

is duplicated by

MDEV-14791 Crash with order by expression containing window functions

Closed

relates to

MDEV-13649 Server crashes in set_field_to_null_with_conversions or in Field::set_notnull

Closed

MDEV-15212 server crashed, query using ORDER BY (expression with window function)

Closed

Activity

Ascending order - Click to sort in descending order

mauerparker created issue - 2018-07-12 09:22

Alice Sherepa made changes - 2018-07-12 13:18

Field	Original Value	New Value
Assignee		Alice Sherepa [ alice ]

Alice Sherepa added a comment - 2018-07-12 14:16

Thanks for the report and the the testcase! Reproducible on 10.2, 10.3

 10.2 1cc1d0429da14a041a62

Thread 1 (Thread 0x7f165c153700 (LWP 6780)):

#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

#1  0x000055e10d6d0789 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477

#2  0x000055e10cf68eed in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305

#3  <signal handler called>

#4  0x000055e10cf5fdd1 in set_field_to_null_with_conversions (field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/field_conv.cc:204

#5  0x000055e10cf8f28a in Item::save_in_field (this=0x7f1610015f38, field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/item.cc:6509

#6  0x000055e10cebe631 in save_window_function_values (window_functions=..., tbl=0x7f161012f9a8, rowid_buf=0x7f1610112c40 "\300E\003\020\026\177") at /home/alice/git/10.2/sql/sql_window.cc:2517

#7  0x000055e10cebea68 in compute_window_func (thd=0x7f1610000b00, window_functions=..., cursor_managers=..., tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2636

#8  0x000055e10cebee1c in Window_func_runner::exec (this=0x7f161012dc90, thd=0x7f1610000b00, tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2742

#9  0x000055e10cebef35 in Window_funcs_sort::exec (this=0x7f161012dc88, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2767

#10 0x000055e10cebf3f7 in Window_funcs_computation::exec (this=0x7f161012dc68, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2886

#11 0x000055e10cd7a2c8 in AGGR_OP::end_send (this=0x7f1610018390) at /home/alice/git/10.2/sql/sql_select.cc:26607

#12 0x000055e10cd6548f in sub_select_postjoin_aggr (join=0x7f1610016620, join_tab=0x7f161012c6d0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18438

#13 0x000055e10cd657b3 in sub_select (join=0x7f1610016620, join_tab=0x7f161012c320, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18674

#14 0x000055e10cd64fcb in do_select (join=0x7f1610016620, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18269

#15 0x000055e10cd3fae7 in JOIN::exec_inner (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3595

#16 0x000055e10cd3ef96 in JOIN::exec (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3390

#17 0x000055e10cd40158 in mysql_select (thd=0x7f1610000b00, tables=0x7f1610014b50, wild_num=0, fields=..., conds=0x0, og_num=4, order=0x7f1610015280, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1610016600, unit=0x7f16100046d8, select_lex=0x7f1610004e10) at /home/alice/git/10.2/sql/sql_select.cc:3790

#18 0x000055e10cd34674 in handle_select (thd=0x7f1610000b00, lex=0x7f1610004610, result=0x7f1610016600, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376

#19 0x000055e10ccffe58 in execute_sqlcom_select (thd=0x7f1610000b00, all_tables=0x7f1610014b50) at /home/alice/git/10.2/sql/sql_parse.cc:6474

#20 0x000055e10ccf5b96 in mysql_execute_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:3481

#21 0x000055e10cd03bc9 in mysql_parse (thd=0x7f1610000b00, rawbuf=0x7f1610012478 "SELECT bin(Dv64),\nlag(TnNr) OVER (partition BY Victorys, Points ORDER BY Victorys DESC, Points DESC, TnNR),\nTnNr,\nVictorys,\nPoints,\n(Dv64 >> (lag(TnNr) OVER (partition BY Victorys, Points  ORDER BY Vi"..., length=405, parser_state=0x7f165c152200, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7995

#22 0x000055e10ccf1409 in dispatch_command (command=COM_QUERY, thd=0x7f1610000b00, packet=0x7f16100753d1 "", packet_length=406, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1821

#23 0x000055e10ccefd65 in do_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:1375

#24 0x000055e10ce40b77 in do_handle_one_connection (connect=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1335

#25 0x000055e10ce408f7 in handle_one_connection (arg=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1241

#26 0x000055e10d66de6e in pfs_spawn_thread (arg=0x55e10f6ca680) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862

#27 0x00007f16637266ba in start_thread (arg=0x7f165c153700) at pthread_create.c:333

#28 0x00007f1662bbb41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Alice Sherepa added a comment - 2018-07-12 14:16 Thanks for the report and the the testcase! Reproducible on 10.2, 10.3 10.2 1cc1d0429da14a041a62 Thread 1 (Thread 0x7f165c153700 (LWP 6780)): #0 __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62 #1 0x000055e10d6d0789 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477 #2 0x000055e10cf68eed in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305 #3 <signal handler called> #4 0x000055e10cf5fdd1 in set_field_to_null_with_conversions (field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/field_conv.cc:204 #5 0x000055e10cf8f28a in Item::save_in_field (this=0x7f1610015f38, field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/item.cc:6509 #6 0x000055e10cebe631 in save_window_function_values (window_functions=..., tbl=0x7f161012f9a8, rowid_buf=0x7f1610112c40 "\300E\003\020\026\177") at /home/alice/git/10.2/sql/sql_window.cc:2517 #7 0x000055e10cebea68 in compute_window_func (thd=0x7f1610000b00, window_functions=..., cursor_managers=..., tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2636 #8 0x000055e10cebee1c in Window_func_runner::exec (this=0x7f161012dc90, thd=0x7f1610000b00, tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2742 #9 0x000055e10cebef35 in Window_funcs_sort::exec (this=0x7f161012dc88, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2767 #10 0x000055e10cebf3f7 in Window_funcs_computation::exec (this=0x7f161012dc68, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2886 #11 0x000055e10cd7a2c8 in AGGR_OP::end_send (this=0x7f1610018390) at /home/alice/git/10.2/sql/sql_select.cc:26607 #12 0x000055e10cd6548f in sub_select_postjoin_aggr (join=0x7f1610016620, join_tab=0x7f161012c6d0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18438 #13 0x000055e10cd657b3 in sub_select (join=0x7f1610016620, join_tab=0x7f161012c320, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18674 #14 0x000055e10cd64fcb in do_select (join=0x7f1610016620, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18269 #15 0x000055e10cd3fae7 in JOIN::exec_inner (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3595 #16 0x000055e10cd3ef96 in JOIN::exec (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3390 #17 0x000055e10cd40158 in mysql_select (thd=0x7f1610000b00, tables=0x7f1610014b50, wild_num=0, fields=..., conds=0x0, og_num=4, order=0x7f1610015280, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1610016600, unit=0x7f16100046d8, select_lex=0x7f1610004e10) at /home/alice/git/10.2/sql/sql_select.cc:3790 #18 0x000055e10cd34674 in handle_select (thd=0x7f1610000b00, lex=0x7f1610004610, result=0x7f1610016600, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376 #19 0x000055e10ccffe58 in execute_sqlcom_select (thd=0x7f1610000b00, all_tables=0x7f1610014b50) at /home/alice/git/10.2/sql/sql_parse.cc:6474 #20 0x000055e10ccf5b96 in mysql_execute_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:3481 #21 0x000055e10cd03bc9 in mysql_parse (thd=0x7f1610000b00, rawbuf=0x7f1610012478 "SELECT bin(Dv64),\nlag(TnNr) OVER (partition BY Victorys, Points ORDER BY Victorys DESC, Points DESC, TnNR),\nTnNr,\nVictorys,\nPoints,\n(Dv64 >> (lag(TnNr) OVER (partition BY Victorys, Points ORDER BY Vi"..., length=405, parser_state=0x7f165c152200, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7995 #22 0x000055e10ccf1409 in dispatch_command (command=COM_QUERY, thd=0x7f1610000b00, packet=0x7f16100753d1 "", packet_length=406, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1821 #23 0x000055e10ccefd65 in do_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:1375 #24 0x000055e10ce40b77 in do_handle_one_connection (connect=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1335 #25 0x000055e10ce408f7 in handle_one_connection (arg=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1241 #26 0x000055e10d66de6e in pfs_spawn_thread (arg=0x55e10f6ca680) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862 #27 0x00007f16637266ba in start_thread (arg=0x7f165c153700) at pthread_create.c:333 #28 0x00007f1662bbb41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Alice Sherepa made changes - 2018-07-12 14:16

Link

This issue relates to ~~MDEV-13649~~ [ ~~MDEV-13649~~ ]

Alice Sherepa made changes - 2018-07-13 07:28

Component/s

Optimizer - Window functions [ 13502 ]

Alice Sherepa made changes - 2018-07-13 07:31

Link

This issue relates to ~~MDEV-15212~~ [ ~~MDEV-15212~~ ]

Alice Sherepa added a comment - 2018-07-13 07:32

test case:

CREATE TABLE t1 (id int NOT NULL);

 INSERT INTO t1 VALUES (1), (2), (3), (4);

SELECT 1 FROM t1

ORDER BY lag(id) OVER (order by id) -1;

Alice Sherepa added a comment - 2018-07-13 07:32 test case: CREATE TABLE t1 (id int NOT NULL ); INSERT INTO t1 VALUES (1), (2), (3), (4); SELECT 1 FROM t1 ORDER BY lag(id) OVER ( order by id) -1;

Alice Sherepa made changes - 2018-07-13 07:33

Assignee

Alice Sherepa [ alice ]

Vicentiu Ciorbaru [ cvicentiu ]

Alice Sherepa made changes - 2018-07-13 07:33

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2018-07-13 07:33

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]

Alice Sherepa made changes - 2018-07-13 07:33

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]

Alice Sherepa made changes - 2018-07-13 09:46

Priority

Blocker [ 1 ]

Major [ 3 ]

Alice Sherepa made changes - 2019-03-20 09:41

Affects Version/s

10.4 [ 22408 ]

Alice Sherepa made changes - 2019-03-20 09:41

Fix Version/s

10.4 [ 22408 ]

Alice Sherepa made changes - 2019-03-20 09:41

Assignee

Vicentiu Ciorbaru [ cvicentiu ]

Varun Gupta [ varun ]

Alice Sherepa made changes - 2019-03-20 09:42

Link

This issue relates to ~~MDEV-14791~~ [ ~~MDEV-14791~~ ]

Varun Gupta (Inactive) added a comment - 2019-04-12 11:54

Looks like a duplicate of ~~MDEV-14791~~

Varun Gupta (Inactive) added a comment - 2019-04-12 11:54 Looks like a duplicate of MDEV-14791

Varun Gupta (Inactive) made changes - 2019-05-04 10:24

Link

This issue is duplicated by ~~MDEV-14791~~ [ ~~MDEV-14791~~ ]

Varun Gupta (Inactive) made changes - 2019-05-04 10:25

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Duplicate [ 3 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Varun Gupta (Inactive) made changes - 2019-05-04 10:26

Link

This issue relates to ~~MDEV-14791~~ [ ~~MDEV-14791~~ ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88370 ]

MariaDB v4 [ 154651 ]

People

Assignee:: Varun Gupta (Inactive)

Reporter:: mauerparker

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-07-12 09:22

Updated:: 2019-05-04 10:26

Resolved:: 2019-05-04 10:25

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration