[MDEV-19631] Assertion `0' failed in st_select_lex_unit::optimize or different plan upon 2nd execution of PS with EXPLAIN Created: 2019-05-29  Updated: 2022-03-30  Resolved: 2022-03-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Prepared Statements
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Dmitry Shulga
Resolution: Fixed Votes: 0
Labels: None


 Description    

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2); # Optional, fails either way
 
 
 
PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 HAVING 6 IN ( SELECT 6 UNION SELECT 5 )';
 
EXECUTE stmt;
 
EXECUTE stmt;
 
 
 
# Cleanup
 
DEALLOCATE PREPARE stmt;
 
DROP TABLE t1;

10.1 and 10.2 debug builds fail with the assertion failure:

10.1 aaf53ea0

 
mysqld: /data/src/10.1/sql/sql_union.cc:742: bool st_select_lex_unit::optimize(): Assertion `0' failed.
 
190529 14:59:27 [ERROR] mysqld got signal 6 ;
 
 
 
#6  0x00007fec597ede67 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55b9bcf93d3b "0", file=file@entry=0x55b9bcf93bf8 "/data/src/10.1/sql/sql_union.cc", line=line@entry=742, function=function@entry=0x55b9bcf93f40 <st_select_lex_unit::optimize()::__PRETTY_FUNCTION__> "bool st_select_lex_unit::optimize()") at assert.c:92
 
#7  0x00007fec597edf12 in __GI___assert_fail (assertion=0x55b9bcf93d3b "0", file=0x55b9bcf93bf8 "/data/src/10.1/sql/sql_union.cc", line=742, function=0x55b9bcf93f40 <st_select_lex_unit::optimize()::__PRETTY_FUNCTION__> "bool st_select_lex_unit::optimize()") at assert.c:101
 
#8  0x000055b9bc6b2ac1 in st_select_lex_unit::optimize (this=0x7fec509a91d8) at /data/src/10.1/sql/sql_union.cc:742
 
#9  0x000055b9bc6b2e07 in st_select_lex_unit::exec (this=0x7fec509a91d8) at /data/src/10.1/sql/sql_union.cc:807
 
#10 0x000055b9bc6529fb in mysql_explain_union (thd=0x7fec527d0070, unit=0x7fec509a91d8, result=0x7fec508452b0) at /data/src/10.1/sql/sql_select.cc:24964
 
#11 0x000055b9bc652883 in select_describe (join=0x7fec50845930, need_tmp_table=false, need_order=false, distinct=false, message=0x55b9bcf83aa5 "Impossible HAVING") at /data/src/10.1/sql/sql_select.cc:24935
 
#12 0x000055b9bc634801 in return_zero_rows (join=0x7fec50845930, result=0x7fec508452b0, tables=..., fields=..., send_row=false, select_options=2416184068, info=0x55b9bcf83aa5 "Impossible HAVING", having=0x0, all_fields=...) at /data/src/10.1/sql/sql_select.cc:12624
 
#13 0x000055b9bc61a4e2 in JOIN::exec_inner (this=0x7fec50845930) at /data/src/10.1/sql/sql_select.cc:2687
 
#14 0x000055b9bc619c9b in JOIN::exec (this=0x7fec50845930) at /data/src/10.1/sql/sql_select.cc:2562
 
#15 0x000055b9bc61d229 in mysql_select (thd=0x7fec527d0070, rref_pointer_array=0x7fec509a7b00, tables=0x7fec509a87a8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x7fec509aa5f8, proc_param=0x0, select_options=2416184068, result=0x7fec508452b0, unit=0x7fec509a7158, select_lex=0x7fec509a7858) at /data/src/10.1/sql/sql_select.cc:3499
 
#16 0x000055b9bc652af9 in mysql_explain_union (thd=0x7fec527d0070, unit=0x7fec509a7158, result=0x7fec508452b0) at /data/src/10.1/sql/sql_select.cc:24981
 
#17 0x000055b9bc5e1d0e in execute_sqlcom_select (thd=0x7fec527d0070, all_tables=0x7fec509a87a8) at /data/src/10.1/sql/sql_parse.cc:5890
 
#18 0x000055b9bc5d8443 in mysql_execute_command (thd=0x7fec527d0070) at /data/src/10.1/sql/sql_parse.cc:3038
 
#19 0x000055b9bc60064c in Prepared_statement::execute (this=0x7fec50a15470, expanded_query=0x7fec5b2155f0, open_cursor=false) at /data/src/10.1/sql/sql_prepare.cc:4362
 
#20 0x000055b9bc5ff4b1 in Prepared_statement::execute_loop (this=0x7fec50a15470, expanded_query=0x7fec5b2155f0, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/10.1/sql/sql_prepare.cc:3994
 
#21 0x000055b9bc5fd53a in mysql_sql_stmt_execute (thd=0x7fec527d0070) at /data/src/10.1/sql/sql_prepare.cc:3110
 
#22 0x000055b9bc5d8474 in mysql_execute_command (thd=0x7fec527d0070) at /data/src/10.1/sql/sql_parse.cc:3049
 
#23 0x000055b9bc5e5d5b in mysql_parse (thd=0x7fec527d0070, rawbuf=0x7fec50845088 "EXECUTE stmt", length=12, parser_state=0x7fec5b2161e0) at /data/src/10.1/sql/sql_parse.cc:7469
 
#24 0x000055b9bc5d4455 in dispatch_command (command=COM_QUERY, thd=0x7fec527d0070, packet=0x7fec547f0071 "", packet_length=12) at /data/src/10.1/sql/sql_parse.cc:1499
 
#25 0x000055b9bc5d3213 in do_command (thd=0x7fec527d0070) at /data/src/10.1/sql/sql_parse.cc:1131
 
#26 0x000055b9bc70e72f in do_handle_one_connection (thd_arg=0x7fec527d0070) at /data/src/10.1/sql/sql_connect.cc:1330
 
#27 0x000055b9bc70e475 in handle_one_connection (arg=0x7fec527d0070) at /data/src/10.1/sql/sql_connect.cc:1242
 
#28 0x000055b9bcb41aaa in pfs_spawn_thread (arg=0x7fec58c39c70) at /data/src/10.1/storage/perfschema/pfs.cc:1861
 
#29 0x00007fec5ae944a4 in start_thread (arg=0x7fec5b217700) at pthread_create.c:456
 
#30 0x00007fec598aad0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Non-debug builds of 10.1/10.2 and debug/non-debug builds of 10.3/10.4 don't crash, but they produce a different plan on the 2nd execution of PS:

10.4 7d3a759d

 
EXECUTE stmt;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
EXECUTE stmt;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
 
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL



 Comments   
Comment by Dmitry Shulga [ 2022-03-27 ]

The branch for review is bb-10.2-MDEV-19631

Comment by Oleksandr Byelkin [ 2022-03-30 ]

OK to push

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