[MDEV-21218] Server crashes in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field Created: 2019-12-04  Updated: 2023-12-05  Resolved: 2023-12-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None


 Description    

--source include/have_innodb.inc
 
create table t1 (pk int not null primary key, c7 date, c2 char(1), c4 date, key c7 (c7)) engine=innodb;
 
 
 
delete from t1 where 1< all(select 1 from t1 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'));

fails on debug and non-debug builds

10.3 670c9a3a182cfc3a75bc8e

 
#4  0x000055ac72c36911 in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field (this=0x7ff49b22fc00) at /10.3/sql/item_cmpfunc.h:3233
 
#5  0x000055ac72f16c0b in Item_equal::contains (this=0x7ff448018628, field=0x7ff4480a6d20) at /10.3/sql/item_cmpfunc.cc:6453
 
#6  0x000055ac72eea431 in Item_field::find_item_equal (this=0x7ff448014e18, cond_equal=0x7ff448015318) at /10.3/sql/item.cc:6368
 
#7  0x000055ac72c0ab95 in eliminate_item_equal (thd=0x7ff448000af0, cond=0x0, upper_levels=0x7ff448015318, item_equal=0x7ff448016e40) at /10.3/sql/sql_select.cc:14721
 
#8  0x000055ac72c0b7d2 in substitute_for_best_equal_field (thd=0x7ff448000af0, context_tab=0x1, cond=0x7ff448016e40, cond_equal=0x7ff448015318, table_join_idx=0x7ff4480173e8) at /10.3/sql/sql_select.cc:15021
 
#9  0x000055ac72c0b4b9 in substitute_for_best_equal_field (thd=0x7ff448000af0, context_tab=0x1, cond=0x7ff448015128, cond_equal=0x7ff44808f448, table_join_idx=0x7ff4480173e8) at /10.3/sql/sql_select.cc:14948
 
#10 0x000055ac72c0b4b9 in substitute_for_best_equal_field (thd=0x7ff448000af0, context_tab=0x1, cond=0x7ff44808f360, cond_equal=0x7ff44808f448, table_join_idx=0x7ff4480173e8) at /10.3/sql/sql_select.cc:14948
 
#11 0x000055ac72be6a74 in JOIN::optimize_stage2 (this=0x7ff448015c80) at /10.3/sql/sql_select.cc:2066
 
#12 0x000055ac72be6361 in JOIN::optimize_inner (this=0x7ff448015c80) at /10.3/sql/sql_select.cc:1952
 
#13 0x000055ac72be489d in JOIN::optimize (this=0x7ff448015c80) at /10.3/sql/sql_select.cc:1488
 
#14 0x000055ac72b7474b in st_select_lex::optimize_unflattened_subqueries (this=0x7ff448005148, const_only=false) at /10.3/sql/sql_lex.cc:4074
 
#15 0x000055ac730760e4 in mysql_delete (thd=0x7ff448000af0, table_list=0x7ff4480129c8, conds=0x7ff4480159e0, order_list=0x7ff4480053c0, limit=18446744073709551615, options=0, result=0x0) at /10.3/sql/sql_delete.cc:360
 
#16 0x000055ac72ba0fd9 in mysql_execute_command (thd=0x7ff448000af0) at /10.3/sql/sql_parse.cc:4658
 
#17 0x000055ac72babb42 in mysql_parse (thd=0x7ff448000af0, rawbuf=0x7ff448012818 "delete from t1 where 1< all(select 1 from t1 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'))", length=129, parser_state=0x7ff49b231460, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:7818
 
#18 0x000055ac72b98629 in dispatch_command (command=COM_QUERY, thd=0x7ff448000af0, packet=0x7ff448165661 "delete from t1 where 1< all(select 1 from t1 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'))", packet_length=129, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1856
 
#19 0x000055ac72b96f37 in do_command (thd=0x7ff448000af0) at /10.3/sql/sql_parse.cc:1402
 
#20 0x000055ac72d0f534 in do_handle_one_connection (connect=0x55ac7713da60) at /10.3/sql/sql_connect.cc:1403
 
#21 0x000055ac72d0f270 in handle_one_connection (arg=0x55ac7713da60) at /10.3/sql/sql_connect.cc:1308
 
#22 0x000055ac736bf6d4 in pfs_spawn_thread (arg=0x55ac77088220) at /10.3/storage/perfschema/pfs.cc:1862
 
#23 0x00007ff4a68d26ba in start_thread (arg=0x7ff49b232700) at pthread_create.c:333
 
#24 0x00007ff4a5d6741d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
 



--source include/have_innodb.inc
 
create table t1 (pk int not null primary key, c7 date, c2 char(1), c4 date, key c7 (c7)) engine=innodb;
 
select 1 from t1 where 1< all(select 1 from t1 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'));


#4  0x0000557be00d23ef in Field::eq (this=0x7f511418cd40, field=0x4) at /10.4/sql/field.h:895
 
#5  0x0000557be0146476 in Item_equal::contains (this=0x7f511406e5a8, field=0x7f511418cd40) at /10.4/sql/item_cmpfunc.cc:6657
 
#6  0x0000557be0118165 in Item_field::find_item_equal (this=0x7f5114015908, cond_equal=0x7f5114015ef0) at /10.4/sql/item.cc:6033
 
#7  0x0000557bdfdfbc24 in eliminate_item_equal (thd=0x7f5114000af0, cond=0x0, upper_levels=0x7f5114015ef0, item_equal=0x7f511406cd58) at /10.4/sql/sql_select.cc:15433
 
#8  0x0000557bdfdfc88c in substitute_for_best_equal_field (thd=0x7f5114000af0, context_tab=0x1, cond=0x7f511406cd58, cond_equal=0x7f5114015ef0, table_join_idx=0x7f511406d2e0, do_substitution=true) at /10.4/sql/sql_select.cc:15740
 
#9  0x0000557bdfdfc509 in substitute_for_best_equal_field (thd=0x7f5114000af0, context_tab=0x1, cond=0x7f5114015cf0, cond_equal=0x7f511406e978, table_join_idx=0x7f511406d2e0, do_substitution=true) at /10.4/sql/sql_select.cc:15661
 
#10 0x0000557bdfdfc509 in substitute_for_best_equal_field (thd=0x7f5114000af0, context_tab=0x1, cond=0x7f511406e888, cond_equal=0x7f511406e978, table_join_idx=0x7f511406d2e0, do_substitution=true) at /10.4/sql/sql_select.cc:15661
 
#11 0x0000557bdfdd57e3 in JOIN::optimize_stage2 (this=0x7f51140185a0) at /10.4/sql/sql_select.cc:2378
 
#12 0x0000557bdfdd5019 in JOIN::optimize_inner (this=0x7f51140185a0) at /10.4/sql/sql_select.cc:2261
 
#13 0x0000557bdfdd2952 in JOIN::optimize (this=0x7f51140185a0) at /10.4/sql/sql_select.cc:1598
 
#14 0x0000557bdfd56ae9 in st_select_lex::optimize_unflattened_subqueries (this=0x7f51140132c8, const_only=false) at /10.4/sql/sql_lex.cc:4187
 
#15 0x0000557bdff789d8 in JOIN::optimize_unflattened_subqueries (this=0x7f5114017930) at /10.4/sql/opt_subselect.cc:5512
 
#16 0x0000557bdfdd7087 in JOIN::optimize_stage2 (this=0x7f5114017930) at /10.4/sql/sql_select.cc:2797
 
#17 0x0000557bdfdd5019 in JOIN::optimize_inner (this=0x7f5114017930) at /10.4/sql/sql_select.cc:2261
 
#18 0x0000557bdfdd2952 in JOIN::optimize (this=0x7f5114017930) at /10.4/sql/sql_select.cc:1598
 
#19 0x0000557bdfdddcf8 in mysql_select (thd=0x7f5114000af0, tables=0x7f5114013888, wild_num=1, fields=..., conds=0x7f5114016eb8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f5114017908, unit=0x7f5114004a20, select_lex=0x7f51140132c8) at /10.4/sql/sql_select.cc:4633
 
#20 0x0000557bdfdcd9f4 in handle_select (thd=0x7f5114000af0, lex=0x7f5114004958, result=0x7f5114017908, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:420
 
#21 0x0000557bdfd93f07 in execute_sqlcom_select (thd=0x7f5114000af0, all_tables=0x7f5114013888) at /10.4/sql/sql_parse.cc:6360
 
#22 0x0000557bdfd895ee in mysql_execute_command (thd=0x7f5114000af0) at /10.4/sql/sql_parse.cc:3899
 
#23 0x0000557bdfd98049 in mysql_parse (thd=0x7f5114000af0, rawbuf=0x7f5114013158 "select * from t1 \nwhere 1< all(select 1 from t1 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'))", length=132, parser_state=0x7f515c3a2fe0, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7901
 
#24 0x0000557bdfd831f6 in dispatch_command (command=COM_QUERY, thd=0x7f5114000af0, packet=0x7f51141363b1 "select * from t1 \nwhere 1< all(select 1 from t1 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'))", packet_length=132, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1842
 
#25 0x0000557bdfd81857 in do_command (thd=0x7f5114000af0) at /10.4/sql/sql_parse.cc:1360
 
#26 0x0000557bdff0b621 in do_handle_one_connection (connect=0x557be28ae860) at /10.4/sql/sql_connect.cc:1412
 
#27 0x0000557bdff0b34a in handle_one_connection (arg=0x557be28ae860) at /10.4/sql/sql_connect.cc:1316
 
#28 0x0000557be09108d3 in pfs_spawn_thread (arg=0x557be282c570) at /10.4/storage/perfschema/pfs.cc:1862
 
#29 0x00007f51653166ba in start_thread (arg=0x7f515c3a4700) at pthread_create.c:333
 
#30 0x00007f5163ef941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Igor Babaev [ 2020-01-16 ]

This bug was introduced by the patch for MDEV-6480: Remove conditions for which range optimizer returned SEL_ARG::IMPOSSIBLE (commit be00e279c6061134a33a8099fd69d4304735d02e). The commit comment says:

Let range optimizer remove parts of OR-clauses for which range analysis
 
produced SEL_TREE(IMPOSSIBLE).
 
There is no need to remove parts of AND-clauses: either they are inside
 
of OR (and the whole AND-clause will be removed), or the AND-clause is
 
at the top level, in which case the whole WHERE (or ON) is always FALSE
 
and this is a degenerate case which receives special treatment._
 
 
 
The removal process takes care not to produce 1-way ORs (in that case
 
we substitute the OR for its remaining member).

Actually the removal of always FALSE disjunctive members cannot just inject the only remaining disjunct into the upper AND level. This disjunct may be an AND formula with multiple equalities. They must moved to the multiple equalities of the upper level (multiple equalities are always placed at the and of the AND item containing them). cond_equal for the upper level also must be corrected.

For the reporting query after the call of get_quick_record_count() we have the condition 

multiple equal('a', t1.c2)) and t1.pk < '1' and multiple equal(DATE'0000-00-00', t1.c4)

This is not an expected representation for the AND formula with multiple equalities.
Before the call of get_quick_record_count() the condition is:

t1.c7 = load_file('1') or (t1.c7 = NULL or multiple equal('a', t1.c2)) and t1.pk < '1' and multiple equal(DATE'0000-00-00', t1.c4)

I did not check the test case for 10.1, but I see that the offensive code is there.

Comment by Elena Stepanova [ 2021-05-18 ]

The test case is not directly applicable to 10.2 as it fails with ER_UPDATE_TABLE_USED (Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data); but if two tables are used instead of one, it crashes the same way on 10.2 23cad4d8.

--source include/have_innodb.inc
 
create table t1 (pk int not null primary key, c7 date, c2 char(1), c4 date, key c7 (c7)) engine=innodb;
 
create table t2 (pk int not null primary key, c7 date, c2 char(1), c4 date, key c7 (c7)) engine=innodb;
 
 
delete from t1 where 1< all(select 1 from t2 where c7 in (load_file('1')) or (c4 = 'b' and (c7 = null or c2 = 'a') and pk < '1'));

(can probably be made simpler, I didn't try).

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by JiraAutomate [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by Alice Sherepa [ 2023-12-05 ]

not reproducible on current 10.4 ( d8e6bb00888b1f82c031938f4c8ac5d97f6874c3 ) -11.3

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