[MDEV-16727] Server crashes in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field Created: 2018-07-11  Updated: 2019-02-18  Resolved: 2018-08-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4.0

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Galina Shalygina (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4971 Server crashes in Item_equal_iterator... Closed

 Description   

Reproducible with MyIsam, not with Innodb

CREATE TABLE t1 (i2 int, i1 int, d1 date, d2 date, v1 varchar(1), v2 varchar(1)) engine=MyIsam;
 
INSERT INTO `t1` VALUES (7,9,'2012-03-22','2012-03-22',NULL,NULL),(9,9,'1900-01-01','1900-01-01',NULL,NULL);
 
 
CREATE TABLE t2 (i2 int, i1 int, d1 date, d2 date, v1 varchar(1), v2 varchar(1)) engine=MyIsam;
 
INSERT INTO t2 VALUES (1,7,'1900-01-01','1900-01-01','x','x');
 
 
CREATE TABLE t3 (i2 int, i1 int, d1 date, d2 date, v1 varchar(1), v2 varchar(1)) engine=MyIsam;
 
INSERT INTO t3 VALUES (7,8,'2004-10-02','2004-10-02','g','g');
 
 
CREATE TABLE t4 (d2 date, v1 varchar(1), v2 varchar(1)) engine=MyIsam;
 
 
CREATE TABLE t5 (i2 int, i1 int, d1 date, d2 date, v1 varchar(1), v2 varchar(1)) engine=MyIsam;
 
INSERT INTO `t5` VALUES (2,4,'1984-12-05','1984-12-05','v','v'),(150,62,'2021-03-27','2021-03-27','v','v');
 
 
UPDATE t1 JOIN t2 ON (t1.v1 = t2.v2) LEFT JOIN t5 ON (t1.d1 = t5.d2)
 
SET t1.i1 = 1 
WHERE (t1.v1, t1.v2) IN (SELECT t4.v2,t4.v2 FROM t3 JOIN t4 ON (t3.v2 <> t4.v2) ORDER BY t4.d2);


 10.4 5180eda342c07d44bdc06b9b872f1
 
Thread 1 (Thread 0x7fdbe65d6700 (LWP 9588)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000558a7254d761 in my_write_core (sig=11) at /home/alice/git/10.4/mysys/stacktrace.c:481
 
#2  0x0000558a71d900d1 in handle_fatal_signal (sig=11) at /home/alice/git/10.4/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x0000558a71b33138 in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field (this=0x7fdbe65d4640) at /home/alice/git/10.4/sql/item_cmpfunc.h:3249
 
#5  0x0000558a71de5a99 in Item_equal::contains (this=0x7fdbd405a180, field=0x7fdbd404a0d8) at /home/alice/git/10.4/sql/item_cmpfunc.cc:6365
 
#6  0x0000558a71dbb2c3 in Item_field::find_item_equal (this=0x7fdbd40170b0, cond_equal=0x7fdbd4059f70) at /home/alice/git/10.4/sql/item.cc:6386
 
#7  0x0000558a71b086cc in eliminate_item_equal (thd=0x7fdbd4000b00, cond=0x0, upper_levels=0x7fdbd4059f70, item_equal=0x7fdbd405a360) at /home/alice/git/10.4/sql/sql_select.cc:14506
 
#8  0x0000558a71b09304 in substitute_for_best_equal_field (thd=0x7fdbd4000b00, context_tab=0x1, cond=0x7fdbd405a360, cond_equal=0x7fdbd4059f70, table_join_idx=0x7fdbd4063518) at /home/alice/git/10.4/sql/sql_select.cc:14806
 
#9  0x0000558a71ae649b in JOIN::optimize_stage2 (this=0x7fdbd40576e8) at /home/alice/git/10.4/sql/sql_select.cc:2090
 
#10 0x0000558a71ae5bcb in JOIN::optimize_inner (this=0x7fdbd40576e8) at /home/alice/git/10.4/sql/sql_select.cc:1952
 
#11 0x0000558a71ae3f33 in JOIN::optimize (this=0x7fdbd40576e8) at /home/alice/git/10.4/sql/sql_select.cc:1448
 
#12 0x0000558a71aede43 in mysql_select (thd=0x7fdbd4000b00, tables=0x7fdbd40150a0, wild_num=0, fields=..., conds=0x7fdbd401a710, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7fdbd401ab18, unit=0x7fdbd40049a8, select_lex=0x7fdbd4005120) at /home/alice/git/10.4/sql/sql_select.cc:4261
 
#13 0x0000558a71b9d07d in mysql_multi_update (thd=0x7fdbd4000b00, table_list=0x7fdbd40150a0, fields=0x7fdbd4005250, values=0x7fdbd4005760, conds=0x7fdbd401a710, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7fdbd40049a8, select_lex=0x7fdbd4005120, result=0x7fdbe65d4fd0) at /home/alice/git/10.4/sql/sql_update.cc:1763
 
#14 0x0000558a71aa2f7a in mysql_execute_command (thd=0x7fdbd4000b00) at /home/alice/git/10.4/sql/sql_parse.cc:4638
 
#15 0x0000558a71aae04d in mysql_parse (thd=0x7fdbd4000b00, rawbuf=0x7fdbd4014e88 "UPDATE t1 JOIN t2 ON (t1.v1 = t2.v2) LEFT JOIN t5 ON (t1.d1 = t5.d2)\nSET t1.i1 = 1 \nWHERE (t1.v1, t1.v2) IN (SELECT t4.v2,t4.v2 FROM t3 JOIN t4 ON (t3.v2 <> t4.v2) ORDER BY t4.d2)", length=179, parser_state=0x7fdbe65d55f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.4/sql/sql_parse.cc:8063
 
#16 0x0000558a71a9b1d0 in dispatch_command (command=COM_QUERY, thd=0x7fdbd4000b00, packet=0x7fdbd400b441 "UPDATE t1 JOIN t2 ON (t1.v1 = t2.v2) LEFT JOIN t5 ON (t1.d1 = t5.d2)\nSET t1.i1 = 1 \nWHERE (t1.v1, t1.v2) IN (SELECT t4.v2,t4.v2 FROM t3 JOIN t4 ON (t3.v2 <> t4.v2) ORDER BY t4.d2)", packet_length=179, is_com_multi=false, is_next_command=false) at /home/alice/git/10.4/sql/sql_parse.cc:1847
 
#17 0x0000558a71a99bed in do_command (thd=0x7fdbd4000b00) at /home/alice/git/10.4/sql/sql_parse.cc:1392
 
#18 0x0000558a71c00613 in do_handle_one_connection (connect=0x558a7533eb20) at /home/alice/git/10.4/sql/sql_connect.cc:1402
 
#19 0x0000558a71c00364 in handle_one_connection (arg=0x558a7533eb20) at /home/alice/git/10.4/sql/sql_connect.cc:1308
 
#20 0x0000558a724ddbaa in pfs_spawn_thread (arg=0x558a75346c10) at /home/alice/git/10.4/storage/perfschema/pfs.cc:1862
 
#21 0x00007fdbed0756ba in start_thread (arg=0x7fdbe65d6700) at pthread_create.c:333
 
#22 0x00007fdbec50a41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Galina Shalygina (Inactive) [ 2018-07-28 ]

Running example from the JIRA description it was found that the error occurs in the line:

Item_field *item= (Item_field *) (curr_item->real_item());

because of:

(gdb) print curr_item
 
$1 = (Item *) 0x8f8f8f8f8f8f8f8f

Going up the stack it can be found that curr_field is taken from the list of multiple equalities cond_equal. The mistake is caused by substitute_for_best_equal_field() call for the ON expression.

(gdb) print dbug_print_item(*tab->on_expr_ref)
 
$7 = 0x5555573c30a0 <dbug_item_print_buf> "multiple equal(t1.d1, t5.d2)"

cond_equal is the upper_levels field of this ON expression. The upper_levels field for the ON expression should be the link on the set of multiple equalities used in the WHERE clause of the select.

And it can be seen that it is set to the wrong value.

(gdb) print item_equal->upper_levels
 
$8 = (COND_EQUAL *) 0x7fff7804fd70
 
(gdb) p join->cond_equal
 
$9 = (COND_EQUAL *) 0x7fff78054a90

In and_new_conditions_to_optimized_cond() cond_equal was changed. upper_levels for the ON expression remains the same. To fix it new function that changes a proper link should be created.

Comment by Galina Shalygina (Inactive) [ 2018-07-28 ]

In the simplification process of the test cases it was found that the test case below has the same bug:

CREATE TABLE t1 (a varchar(1));
 
INSERT INTO `t1` VALUES ('x'), ('y'), ('z');
 
 
 
CREATE TABLE t2 (b varchar(1));
 
INSERT INTO t2 VALUES ('x');
 
 
 
CREATE TABLE t3 (c varchar(1));
 
INSERT INTO t3 VALUES ('y');
 
 
 
CREATE TABLE t4 (d varchar(1));
 
INSERT INTO t4 VALUES ('x'), ('z');
 
 
 
SELECT * FROM t1
 
JOIN t2 ON (t1.a=t2.b)
 
LEFT JOIN t3 ON (t1.a=t3.c)
 
WHERE (t1.a) IN
 
(
 
  SELECT t4.d
 
  FROM t4
 
  ORDER BY t4.d
 
);

The fix suggested below causes another server crash in the make_join_statistics() method because of: 

DBUG_ASSERT(!join->conds || !join->cond_equal ||
 
            !join->cond_equal->current_level.elements ||
 
            (join->conds->type() == Item::COND_ITEM &&
 
            ((Item_cond*) (join->conds))->functype() ==
 
             Item_func::COND_AND_FUNC && 
             join->cond_equal ==
 
             &((Item_cond_and *) (join->conds))->m_cond_equal) ||
 
             (join->conds->type() == Item::FUNC_ITEM &&
 
             ((Item_func*) (join->conds))->functype() ==
 
             Item_func::MULT_EQUAL_FUNC &&
 
             join->cond_equal->current_level.elements == 1 &&
 
             join->cond_equal->current_level.head() == join->conds));
 
 
 
(gdb) print join->cond_equal->current_level.elements
 
$1 = 2013355672

That means that join->cond_equal field is set to the wrong value.
That happens because of the wrong cond_equal initialization in the and_new_conditions_to_optimized_cond() method. With changing the initialization method the fix suggested before becomes unnecessary.

Comment by Igor Babaev [ 2018-07-29 ]

Ok to push

Comment by Galina Shalygina (Inactive) [ 2018-08-01 ]

Pushed in 10.4

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