|
CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
|
|
# To reproduce, the table should have 0 or 1 rows
|
INSERT INTO t1 VALUES (1);
|
|
CREATE TABLE t2 (i2 INT, c2 VARCHAR(3), KEY(i2,c2)) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (1,'abc'),(2,'xyz');
|
|
CREATE TABLE t3 (pk3 INT PRIMARY KEY, c3 VARCHAR(3)) ENGINE=MyISAM;
|
INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
|
|
SELECT * FROM t1 WHERE i1 NOT IN (
|
SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
|
);
|
|
5.5 14fca28ea44dabf62e75e2aa9a90d71bd7be45c0
|
#3 <signal handler called>
|
#4 0x0000000000646f00 in create_ref_for_key (join=0x7faae1db5078, j=0x7faae1db9710, org_keyuse=0x7faae1c914b0, allow_full_scan=true, used_tables=4611686018427387906) at /data/src/5.5/sql/sql_select.cc:8066
|
#5 0x00000000006466b6 in get_best_combination (join=0x7faae1db5078) at /data/src/5.5/sql/sql_select.cc:7850
|
#6 0x000000000063cc2d in make_join_statistics (join=0x7faae1db5078, tables_list=..., conds=0x0, keyuse_array=0x7faae1db5398) at /data/src/5.5/sql/sql_select.cc:3837
|
#7 0x0000000000633910 in JOIN::optimize (this=0x7faae1db5078) at /data/src/5.5/sql/sql_select.cc:1234
|
#8 0x00000000005f92a1 in st_select_lex::optimize_unflattened_subqueries (this=0x7faae2e77a68, const_only=false) at /data/src/5.5/sql/sql_lex.cc:3549
|
#9 0x000000000074535a in JOIN::optimize_unflattened_subqueries (this=0x7faae1d81328) at /data/src/5.5/sql/opt_subselect.cc:4984
|
#10 0x00000000006353b5 in JOIN::optimize (this=0x7faae1d81328) at /data/src/5.5/sql/sql_select.cc:1670
|
#11 0x000000000063a2a7 in mysql_select (thd=0x7faae2e74060, rref_pointer_array=0x7faae2e77cd8, tables=0x7faae1d48308, wild_num=1, fields=..., conds=0x7faae1d81190, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7faae1d81308, unit=0x7faae2e77388, select_lex=0x7faae2e77a68) at /data/src/5.5/sql/sql_select.cc:3085
|
#12 0x0000000000630b94 in handle_select (thd=0x7faae2e74060, lex=0x7faae2e772d8, result=0x7faae1d81308, setup_tables_done_option=0) at /data/src/5.5/sql/sql_select.cc:319
|
#13 0x000000000060a08b in execute_sqlcom_select (thd=0x7faae2e74060, all_tables=0x7faae1d48308) at /data/src/5.5/sql/sql_parse.cc:4686
|
#14 0x00000000006033fe in mysql_execute_command (thd=0x7faae2e74060) at /data/src/5.5/sql/sql_parse.cc:2234
|
#15 0x000000000060cc56 in mysql_parse (thd=0x7faae2e74060, rawbuf=0x7faae1d48078 "SELECT * FROM t1 WHERE i1 NOT IN ( \nSELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 \n)", length=98, parser_state=0x7faae93b1650) at /data/src/5.5/sql/sql_parse.cc:5931
|
#16 0x000000000060098d in dispatch_command (command=COM_QUERY, thd=0x7faae2e74060, packet=0x7faae3b4e061 "SELECT * FROM t1 WHERE i1 NOT IN ( \nSELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 \n)", packet_length=98) at /data/src/5.5/sql/sql_parse.cc:1079
|
#17 0x00000000005ffb47 in do_command (thd=0x7faae2e74060) at /data/src/5.5/sql/sql_parse.cc:793
|
#18 0x000000000070213b in do_handle_one_connection (thd_arg=0x7faae2e74060) at /data/src/5.5/sql/sql_connect.cc:1268
|
#19 0x0000000000701ec8 in handle_one_connection (arg=0x7faae2e74060) at /data/src/5.5/sql/sql_connect.cc:1184
|
#20 0x0000000000944c7d in pfs_spawn_thread (arg=0x7faae3b7a3a0) at /data/src/5.5/storage/perfschema/pfs.cc:1015
|
#21 0x00007faae8fee064 in start_thread (arg=0x7faae93b2700) at pthread_create.c:309
|
#22 0x00007faae741462d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
|
The problem appeared in 5.5 tree with this commit:
commit 2e7ba70a94b5950a7d1d733c177d1b2a24916213
|
Author: Igor Babaev
|
Date: Sat Apr 22 10:30:55 2017 -0700
|
|
Fixed the bug mdev-10693.
|
Another test case, crashes both with MyISAM and InnoDB (and with Aria too):
CREATE TABLE t1 (pk1 INT, a1 VARCHAR(3), b1 VARCHAR(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1));
|
INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
|
|
CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2));
|
INSERT INTO t2 VALUES (1,'abc'),(2,'xyz');
|
|
SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 1 );
|
|