[MDEV-16751] Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 5.5.61, 10.0.36, 10.1.35, 10.2.17, 10.3.10, 10.4.0
Component/s: Optimizer
Labels:
None

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

Attachments

Issue Links

relates to

MDEV-12838 Wrong results with materialization=on (with subquery and joins)

Closed

MDEV-15247 Crash when SET NAMES 'utf8' is set

Closed

Activity

Ascending order - Click to sort in descending order

Varun Gupta (Inactive) added a comment - 2018-07-13 11:53

Simplified query

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

Varun Gupta (Inactive) added a comment - 2018-07-13 11:53 Simplified query SELECT 1 FROM t1 where t1.id IN ( SELECT t3.i1 FROM t3 WHERE t3.i1 = t3.i2);

Varun Gupta (Inactive) added a comment - 2018-07-14 21:31 - edited

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.

Varun Gupta (Inactive) added a comment - 2018-07-14 21:31 - edited 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.

Varun Gupta (Inactive) added a comment - 2018-07-17 21:06 - edited

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.

Varun Gupta (Inactive) added a comment - 2018-07-17 21:06 - edited 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.

Varun Gupta (Inactive) added a comment - 2018-07-20 11:18 - edited

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

Varun Gupta (Inactive) added a comment - 2018-07-20 11:18 - edited Patch http://lists.askmonty.org/pipermail/commits/2018-July/012720.html

Sergei Petrunia added a comment - 2018-07-25 16:50

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)

Sergei Petrunia added a comment - 2018-07-25 16:50 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)

Varun Gupta (Inactive) added a comment - 2018-07-25 19:48 - edited

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);

Varun Gupta (Inactive) added a comment - 2018-07-25 19:48 - edited 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);

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Alice Sherepa

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-07-13 10:57

Updated:: 2019-05-30 10:55

Resolved:: 2018-07-25 09:22

Git Integration

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

MariaDB Server