[#MDEV-31255] ER_GET_ERRNO or server crash upon EXPLAIN DELETE with fulltext search subquery

Before 11.1 default settings are sufficient. On 11.1 the failure stopped happening with default settings somewhere around ~~MDEV-7487~~ or the following commits. However, it can still be reproduced with exists_to_in=off. The test case accounts for both by using an executable comment. In the final test case it can be replaced with per-version adjustments.

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b CHAR(255), FULLTEXT(b));

INSERT INTO t2 VALUES ('foo'),('bar');

# The switch setting is only needed for 11.1+, where the default plans apparently changed

/*!110100 SET optimizer_switch='exists_to_in=off' */;

EXPLAIN DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE MATCH (b) AGAINST ('qux' IN NATURAL LANGUAGE MODE));

# Cleanup

DROP TABLE t1, t2;

10.4 de703a2b with MyISAM

 query 'EXPLAIN DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE MATCH (b) AGAINST ('qux' IN NATURAL LANGUAGE MODE))' failed: ER_GET_ERRNO (1030): Got error -1 "Internal error < 0 (Not system error)" from storage engine MyISAM

10.4 de703a2b with InnoDB
#3 <signal handler called>
#4 0x00005575e710f31b in ha_innobase::ft_read (this=0x61d00025d0a8, buf=0x6190000e56b8 "\377", ' ' <repeats 199 times>...) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:10139
#5 0x00005575e61349fb in handler::ha_ft_read (this=0x61d00025d0a8, buf=0x6190000e56b8 "\377", ' ' <repeats 199 times>...) at /data/src/10.4/sql/sql_class.h:6733
#6 0x00005575e60ef3cd in join_ft_read_first (tab=0x62b0000a51b8) at /data/src/10.4/sql/sql_select.cc:21971
#7 0x00005575e60e699f in sub_select (join=0x62b0000a3810, join_tab=0x62b0000a51b8, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20884
#8 0x00005575e60e49aa in do_select (join=0x62b0000a3810, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:20410
#9 0x00005575e6073843 in JOIN::exec_inner (this=0x62b0000a3810) at /data/src/10.4/sql/sql_select.cc:4605
#10 0x00005575e6070e4a in JOIN::exec (this=0x62b0000a3810) at /data/src/10.4/sql/sql_select.cc:4387
#11 0x00005575e6989232 in subselect_single_select_engine::exec (this=0x62b0000a35d8) at /data/src/10.4/sql/item_subselect.cc:4021
#12 0x00005575e6963c5c in Item_subselect::exec (this=0x62b0000a3428) at /data/src/10.4/sql/item_subselect.cc:758
#13 0x00005575e696e2fa in Item_exists_subselect::val_int (this=0x62b0000a3428) at /data/src/10.4/sql/item_subselect.cc:1717
#14 0x00005575e6bc0968 in mysql_delete (thd=0x62b00009a208, table_list=0x62b0000a13d8, conds=0x62b0000a3428, order_list=0x62b00009ec20, limit=18446744073709551615, options=4, result=0x0) at /data/src/10.4/sql/sql_delete.cc:433
#15 0x00005575e5fa87c0 in mysql_execute_command (thd=0x62b00009a208) at /data/src/10.4/sql/sql_parse.cc:4805
#16 0x00005575e5fbe849 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1228 "EXPLAIN DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE MATCH (b) AGAINST ('qux' IN NATURAL LANGUAGE MODE))", length=111, parser_state=0x7faab4472860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7998
#17 0x00005575e5f94cf5 in dispatch_command (command=COM_QUERY, thd=0x62b00009a208, packet=0x629000299209 "EXPLAIN DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE MATCH (b) AGAINST ('qux' IN NATURAL LANGUAGE MODE))", packet_length=111, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
#18 0x00005575e5f9186e in do_command (thd=0x62b00009a208) at /data/src/10.4/sql/sql_parse.cc:1378
#19 0x00005575e638ff5c in do_handle_one_connection (connect=0x608000000ba8) at /data/src/10.4/sql/sql_connect.cc:1420
#20 0x00005575e638f873 in handle_one_connection (arg=0x608000000ba8) at /data/src/10.4/sql/sql_connect.cc:1324
#21 0x00005575e6ffc004 in pfs_spawn_thread (arg=0x615000006208) at /data/src/10.4/storage/perfschema/pfs.cc:1869
#22 0x00007faac90a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#23 0x00007faac91285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible on all existing versions (with the above-mentioned variation), including earlier minor releases, debug- and non-debug builds alike.

[MDEV-31255] ER_GET_ERRNO or server crash upon EXPLAIN DELETE with fulltext search subquery Created: 2023-05-12 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Data Manipulation - Delete, Full-text Search
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.0

[MDEV-31255] ER_GET_ERRNO or server crash upon EXPLAIN DELETE with fulltext search subquery Created: 2023-05-12 Updated: 2023-11-28