Details
Major Description
I only found MDEV-26835 as possibly related, but it's still too different and there is no tentative patch to check if it fixes both. If it turns out to be the same issue, feel free to close this one as a duplicate.
--source include/have_sequence.inc
|
|
|
CREATE TABLE t1 (a CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES ('1'),('2'); |
|
|
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=MyISAM; |
ALTER TABLE t2 DISABLE KEYS; |
INSERT INTO t2 VALUES (1),(2),(3); |
ALTER TABLE t2 ENABLE KEYS; |
|
|
CREATE TABLE t3 (c INT) ENGINE=MyISAM; |
INSERT INTO t3 (c) SELECT seq FROM seq_1_to_101; |
|
|
SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq); |
|
|
# Cleanup
|
DROP TABLE t1, t2, t3; |
|
10.3 ca001cf2 |
#3 <signal handler called>
|
#4 0x00005589cdb37545 in heap_clear (info=0x0) at /data/src/10.3/storage/heap/hp_clear.c:27
|
#5 0x00005589cdb1d426 in ha_heap::delete_all_rows (this=0x61a0000198a8) at /data/src/10.3/storage/heap/ha_heap.cc:404
|
#6 0x00005589cd604e5d in handler::ha_delete_all_rows (this=0x61a0000198a8) at /data/src/10.3/sql/handler.cc:4451
|
#7 0x00005589ccdc5693 in mysql_derived_fill (thd=0x62a0000ba208, lex=0x62a0000be000, derived=0x62b0000047e8) at /data/src/10.3/sql/sql_derived.cc:1151
|
#8 0x00005589ccdbf3e7 in mysql_handle_single_derived (lex=0x62a0000be000, derived=0x62b0000047e8, phases=96) at /data/src/10.3/sql/sql_derived.cc:193
|
#9 0x00005589ccf877f6 in st_join_table::preread_init (this=0x6290001393d8) at /data/src/10.3/sql/sql_select.cc:12982
|
#10 0x00005589ccfb6a10 in sub_select (join=0x62b000005250, join_tab=0x6290001393d8, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19920
|
#11 0x00005589ccfb86a7 in evaluate_join_record (join=0x62b000005250, join_tab=0x629000139048, error=0) at /data/src/10.3/sql/sql_select.cc:20179
|
#12 0x00005589ccfb75fe in sub_select (join=0x62b000005250, join_tab=0x629000139048, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19991
|
#13 0x00005589ccfb4fe9 in do_select (join=0x62b000005250, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:19490
|
#14 0x00005589ccf48da5 in JOIN::exec_inner (this=0x62b000005250) at /data/src/10.3/sql/sql_select.cc:4190
|
#15 0x00005589ccf46750 in JOIN::exec (this=0x62b000005250) at /data/src/10.3/sql/sql_select.cc:3984
|
#16 0x00005589ccf4a0e2 in mysql_select (thd=0x62a0000ba208, tables=0x62b000000518, wild_num=1, fields=..., conds=0x62b000004e90, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b000005220, unit=0x62a0000be0c0, select_lex=0x62a0000be880) at /data/src/10.3/sql/sql_select.cc:4393
|
#17 0x00005589ccf2005b in handle_select (thd=0x62a0000ba208, lex=0x62a0000be000, result=0x62b000005220, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:372
|
#18 0x00005589cce946e6 in execute_sqlcom_select (thd=0x62a0000ba208, all_tables=0x62b000000518) at /data/src/10.3/sql/sql_parse.cc:6340
|
#19 0x00005589cce824b7 in mysql_execute_command (thd=0x62a0000ba208) at /data/src/10.3/sql/sql_parse.cc:3871
|
#20 0x00005589cce9e180 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq)", length=118, parser_state=0x7fa4a35699b0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7855
|
#21 0x00005589cce74faf in dispatch_command (command=COM_QUERY, thd=0x62a0000ba208, packet=0x629000127209 "", packet_length=118, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852
|
#22 0x00005589cce71b47 in do_command (thd=0x62a0000ba208) at /data/src/10.3/sql/sql_parse.cc:1398
|
#23 0x00005589cd239f5c in do_handle_one_connection (connect=0x6080000008a8) at /data/src/10.3/sql/sql_connect.cc:1404
|
#24 0x00005589cd239889 in handle_one_connection (arg=0x6080000008a8) at /data/src/10.3/sql/sql_connect.cc:1309
|
#25 0x00005589ce820bd5 in pfs_spawn_thread (arg=0x615000005808) at /data/src/10.3/storage/perfschema/pfs.cc:1869
|
#26 0x00007fa4b80a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
|
#27 0x00007fa4b81285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
|
Reproducible with MyISAM and Aria, but not with InnoDB (possibly because of DISABLE/ENABLE KEYS which is somehow important there).
Reproducible on all existing versions, including earlier minor releases, debug- and non-debug alike.
Plan:
EXPLAIN EXTENDED
|
SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where |
1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 Using where; FirstMatch(t1) |
3 LATERAL DERIVED t2 ref b b 5 test.t1.a 1 100.00 Using where; Using index; Using temporary; Using filesort |
4 SUBQUERY t3 ALL NULL NULL NULL NULL 101 100.00 |
Warnings:
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <cache>(!<in_optimizer>(1,exists(/* select#4 */ select 1 from `test`.`t3` limit 1))) and `test`.`t2`.`b` = `test`.`t1`.`a` group by `test`.`t2`.`b`) `sq`) where `test`.`t1`.`a` = `sq`.`b` |