[MDEV-16751] Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 Created: 2018-07-13  Updated: 2019-05-30  Resolved: 2018-07-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 5.5.61, 10.0.36, 10.1.35, 10.2.17, 10.3.10, 10.4.0

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

Issue Links:
Relates
relates to MDEV-12838 Wrong results with materialization=on... Closed
relates to MDEV-15247 Crash when SET NAMES 'utf8' is set Closed

 Description    

set join_cache_level=3 ;
 
set sql_mode='';
 
 
 
CREATE TABLE t1 ( id int NOT NULL ) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (11),(12),(13);
 
 
 
CREATE TABLE t2 ( id int NOT NULL ,i1 int) ENGINE=InnoDB;
 
INSERT INTO t2  VALUES (11,8),(12,5),(13,8);
 
 
 
CREATE TABLE t3 (i1 int, i2 int NOT NULL, v1 varchar(1)) ENGINE=InnoDB;
 
INSERT INTO t3 VALUES  (9,6,'p');
 
 
 
CREATE TABLE t4 (i1 int) ENGINE=InnoDB;
 
CREATE TABLE t5 (i1 int) ENGINE=InnoDB;
 
 
 
SELECT 1
 
FROM t1 JOIN t2
 
  ON (t1.id IN (SELECT t3.i1 FROM t3 
                WHERE (t3.i1 = t3.i2 AND t3.v1 IN (SELECT 1 FROM (t4 JOIN t5  ON (t4.i1 = t5.i1))))));

5.5 2fbf2277ffec86d69f793

Thread 1 (Thread 0x7f035433e700 (LWP 23114)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000000000d18571 in my_write_core (sig=11) at /home/alice/git/5.5/mysys/stacktrace.c:457
 
#2  0x00000000007d9b7e in handle_fatal_signal (sig=11) at /home/alice/git/5.5/sql/signal_handler.cc:262
 
#3  <signal handler called>
 
#4  0x000000000065f2a9 in st_join_table::cleanup (this=0x7f0343d9f860) at /home/alice/git/5.5/sql/sql_select.cc:10638
 
#5  0x00000000006601d6 in JOIN::cleanup (this=0x7f0343d38078, full=true) at /home/alice/git/5.5/sql/sql_select.cc:11057
 
#6  0x000000000064ab6c in JOIN::destroy (this=0x7f0343d38078) at /home/alice/git/5.5/sql/sql_select.cc:2965
 
#7  0x00000000006cf3de in st_select_lex::cleanup (this=0x7f034d392a68) at /home/alice/git/5.5/sql/sql_union.cc:1018
 
#8  0x000000000064b204 in mysql_select (thd=0x7f034d38f060, rref_pointer_array=0x7f034d392d08, tables=0x7f0343d52300, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f0343d0eb88, unit=0x7f034d392388, select_lex=0x7f034d392a68) at /home/alice/git/5.5/sql/sql_select.cc:3138
 
#9  0x00000000006411f1 in handle_select (thd=0x7f034d38f060, lex=0x7f034d3922d8, result=0x7f0343d0eb88, setup_tables_done_option=0) at /home/alice/git/5.5/sql/sql_select.cc:323
 
#10 0x00000000006176ac in execute_sqlcom_select (thd=0x7f034d38f060, all_tables=0x7f0343d52300) at /home/alice/git/5.5/sql/sql_parse.cc:4678
 
#11 0x0000000000610166 in mysql_execute_command (thd=0x7f034d38f060) at /home/alice/git/5.5/sql/sql_parse.cc:2224
 
#12 0x000000000061a5c6 in mysql_parse (thd=0x7f034d38f060, rawbuf=0x7f0343d52078 "SELECT 1\nFROM t1 JOIN t2\nON (t1.id IN (SELECT t3.i1 FROM t3 \nWHERE (t3.i1 = t3.i2 AND t3.v1 IN (SELECT 1 FROM (t4 JOIN t5  ON (t4.i1 = t5.i1))))))", length=146, parser_state=0x7f035433d670) at /home/alice/git/5.5/sql/sql_parse.cc:5923
 
#13 0x000000000060d4d4 in dispatch_command (command=COM_QUERY, thd=0x7f034d38f060, packet=0x7f034c1bb061 "SELECT 1\nFROM t1 JOIN t2\nON (t1.id IN (SELECT t3.i1 FROM t3 \nWHERE (t3.i1 = t3.i2 AND t3.v1 IN (SELECT 1 FROM (t4 JOIN t5  ON (t4.i1 = t5.i1))))))", packet_length=146) at /home/alice/git/5.5/sql/sql_parse.cc:1066
 
#14 0x000000000060c645 in do_command (thd=0x7f034d38f060) at /home/alice/git/5.5/sql/sql_parse.cc:793
 
#15 0x00000000007222a2 in do_handle_one_connection (thd_arg=0x7f034d38f060) at /home/alice/git/5.5/sql/sql_connect.cc:1268
 
#16 0x000000000072200b in handle_one_connection (arg=0x7f034d38f060) at /home/alice/git/5.5/sql/sql_connect.cc:1184
 
#17 0x0000000000cc440f in pfs_spawn_thread (arg=0x7f034d3b2ca0) at /home/alice/git/5.5/storage/perfschema/pfs.cc:1015
 
#18 0x00007f03536eb6ba in start_thread (arg=0x7f035433e700) at pthread_create.c:333
 
#19 0x00007f0352b9241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
 



10.3
 
Thread 1 (Thread 0x7f91d0eea700 (LWP 24500)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x00005644d7c5a47d in my_write_core (sig=11) at /home/alice/git/10.3/mysys/stacktrace.c:481
 
#2  0x00005644d749dd8d in handle_fatal_signal (sig=11) at /home/alice/git/10.3/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x00005644d73b0272 in TABLE_LIST::is_with_table_recursive_reference (this=0xa5a5a5a5a5a5a5a5) at /home/alice/git/10.3/sql/sql_cte.cc:1121
 
#5  0x00005644d7213c9b in st_join_table::cleanup (this=0x7f91880570d8) at /home/alice/git/10.3/sql/sql_select.cc:12273
 
#6  0x00005644d7214e70 in JOIN::cleanup (this=0x7f9188045038, full=true) at /home/alice/git/10.3/sql/sql_select.cc:12710
 
#7  0x00005644d71fe165 in JOIN::destroy (this=0x7f9188045038) at /home/alice/git/10.3/sql/sql_select.cc:4047
 
#8  0x00005644d72a6747 in st_select_lex::cleanup (this=0x7f9188005118) at /home/alice/git/10.3/sql/sql_union.cc:1952
 
#9  0x00005644d71fe7ad in mysql_select (thd=0x7f9188000b00, tables=0x7f9188014f88, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f918801a810, unit=0x7f91880049a8, select_lex=0x7f9188005118) at /home/alice/git/10.3/sql/sql_select.cc:4232
 
#10 0x00005644d71f0846 in handle_select (thd=0x7f9188000b00, lex=0x7f91880048e0, result=0x7f918801a810, setup_tables_done_option=0) at /home/alice/git/10.3/sql/sql_select.cc:382
 
#11 0x00005644d71bb1c8 in execute_sqlcom_select (thd=0x7f9188000b00, all_tables=0x7f9188014f88) at /home/alice/git/10.3/sql/sql_parse.cc:6542
 
#12 0x00005644d71b1621 in mysql_execute_command (thd=0x7f9188000b00) at /home/alice/git/10.3/sql/sql_parse.cc:3765
 
#13 0x00005644d71bf0a8 in mysql_parse (thd=0x7f9188000b00, rawbuf=0x7f9188014d08 "SELECT 1\nFROM t1 JOIN t2\nON (t1.id IN (SELECT t3.i1 FROM t3 \nWHERE (t3.i1 = t3.i2 AND t3.v1 IN (SELECT 1 FROM (t4 JOIN t5  ON (t4.i1 = t5.i1))))))", length=146, parser_state=0x7f91d0ee95f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:8073
 
#14 0x00005644d71ac0f6 in dispatch_command (command=COM_QUERY, thd=0x7f9188000b00, packet=0x7f9188125ef1 "SELECT 1\nFROM t1 JOIN t2\nON (t1.id IN (SELECT t3.i1 FROM t3 \nWHERE (t3.i1 = t3.i2 AND t3.v1 IN (SELECT 1 FROM (t4 JOIN t5  ON (t4.i1 = t5.i1))))))", packet_length=146, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:1847
 
#15 0x00005644d71aab13 in do_command (thd=0x7f9188000b00) at /home/alice/git/10.3/sql/sql_parse.cc:1392
 
#16 0x00005644d7310a37 in do_handle_one_connection (connect=0x5644da7ced00) at /home/alice/git/10.3/sql/sql_connect.cc:1402
 
#17 0x00005644d7310788 in handle_one_connection (arg=0x5644da7ced00) at /home/alice/git/10.3/sql/sql_connect.cc:1308
 
#18 0x00005644d7bea992 in pfs_spawn_thread (arg=0x5644da7d7070) at /home/alice/git/10.3/storage/perfschema/pfs.cc:1862
 
#19 0x00007f91d76e36ba in start_thread (arg=0x7f91d0eea700) at pthread_create.c:333
 
#20 0x00007f91d6b7841d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Varun Gupta (Inactive) [ 2018-07-13 ]

Simplified query 

SELECT 1 FROM t1 where t1.id IN (SELECT t3.i1 FROM t3 WHERE t3.i1 = t3.i2);

Comment by Varun Gupta (Inactive) [ 2018-07-14 ]

So after a bit of observation I find out that create_hj_key_for_table returns error, that is we are not able to find a suitable keyuse to create a hash key.

Lets look at the query

SELECT 1 FROM t1 where t1.id IN (SELECT t3.i1 FROM t3 WHERE t3.i1 = t3.i2);

Subquery's where clause : t3.i1 = t3.i2
IN equality: t1.id = t3.i1

The where clause after merging of the query would be t3.i1 = t3.i2 and t1.id = t3.i1

So we have two different Item_field objects for t3.i1
when we run the function optimize_cond we create multiple equalities for each equality predicate

  • t3.i1 = t3.i2 would first check if there is a multiple equality that already contains field t3.i1 or t3.i2. It finds none and so we create a new multiple equality and that is multiple_equal(t3.i1,t3.i2)
  • t1.id = t3.i1 would do the same check , it finds the equality multiple_equal(t3.i1,t3.i2) that contains t3.i1 , so we only add t1.id to it , the multiple equality then would be (t3.i1,t3.i2,t1.id).

So the multiple equality (t3.i1,t3.i2,t1.id) here contains the object of t3.i1 that was involved in the WHERE clause of the subquery.

Comment by Varun Gupta (Inactive) [ 2018-07-17 ] 

set join_cache_level=2;
 
CREATE TABLE t1 ( id int NOT NULL , key (id)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
 
CREATE TABLE t3 (i1 int NOT NULL, i2 int NOT NULL) ENGINE=InnoDB;
 
INSERT INTO t3 VALUES  (11,11),(12,12),(13,13);


explain
 
SELECT 1 FROM t1 where t1.id IN (SELECT t3.i1 FROM t3 WHERE t3.i1 = t3.i2);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
1	PRIMARY	t1	index	id	id	4	NULL	9	Using where; Using index; Using join buffer (flat, BNL join)
 
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	3	Using where


explain
 
SELECT 1 FROM t1 where t1.id IN (SELECT t3.i1 FROM t3 );
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
1	PRIMARY	t1	ref	id	id	4	test.t3.i1	1	Using index
 
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	3

So here another interesting thing to notice that for the first query we don't use ref access, instead we use index.
I checked this is again due to the reason we don't find any suitable keyuse to create a ref access on.

Comment by Varun Gupta (Inactive) [ 2018-07-20 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-July/012720.html

Comment by Sergei Petrunia [ 2018-07-25 ]

TODO: Check what happens with mergeable VIEWs. Will we get Item_direct_ref objects instead ot Item_field objects ? (I did a check when reviewing and it seemed to work... didn't note why exactly though)

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

Using a mergeable view causes a crash here

set join_cache_level=4;
 
CREATE TABLE t1 ( id int NOT NULL);
 
INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
 
CREATE TABLE t3 (i1 int NOT NULL, i2 int NOT NULL);
 
INSERT INTO t3 VALUES  (11,11),(12,12),(13,13);
 
CREATE VIEW v AS SELECT t3.i1 FROM t3 where t3.i1 = t3.i2;
 
explain SELECT 1 FROM t1 where t1.id IN (SELECT v.i1 from v);

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