|
The test case uses in_predicate_conversion_threshold to make the WHERE expression shorter, but the problem is not limited to the extremely low values of the variable, it generally affects queries which have a longer IN list than in_predicate_conversion_threshold.
The failure is reproducible with the provided test case on 10.3-10.4, not reproducible on 10.5. The plan is quite different on 10.5, possibly with the precise adjustments to the query and/or optimizer switch the same failing plan can be achieved on 10.5 too.
Both big_tables and tmp_memory_table_size are set in the test case to cover all versions at once. For 10.3-10.4 big_tables should work, and for 10.5 (if the problem were reproducible there) tmp_memory_table_size replaces the deprecated big_tables.
There is an open bug report MDEV-23828 for this assertion failure, but I'm not sure whether it's related – the test case is quite different there. If they turn out to be the same, please feel free to close any of them as a duplicate, but check both test cases to verify the fix.
SET big_tables= 1, tmp_memory_table_size= 0;
|
SET in_predicate_conversion_threshold= 2;
|
CREATE TABLE t1 (f VARCHAR(128) CHARSET utf8) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('foo'),('bar');
|
SELECT * FROM t1 WHERE f NOT IN ('qux', REPEAT('a',333));
|
|
# Cleanup
|
DROP TABLE t1;
|
|
10.3 469a249a
|
mysqld: /data/src/10.3/sql/item_subselect.cc:5020: bool subselect_hash_sj_engine::init(List<Item>*, uint): Assertion `0' failed.
|
201008 16:43:56 [ERROR] mysqld got signal 6 ;
|
|
#7 0x00007f067162bf36 in __GI___assert_fail (assertion=0x557afcd314f8 "0", file=0x557afcd32078 "/data/src/10.3/sql/item_subselect.cc", line=5020, function=0x557afcd33f58 "bool subselect_hash_sj_engine::init(List<Item>*, uint)") at assert.c:101
|
#8 0x0000557afc3094ee in subselect_hash_sj_engine::init (this=0x7f06500a7738, tmp_columns=0x7f0650014638, subquery_id=2) at /data/src/10.3/sql/item_subselect.cc:5020
|
#9 0x0000557afc3058f5 in Item_in_subselect::setup_mat_engine (this=0x7f06500164e8) at /data/src/10.3/sql/item_subselect.cc:3431
|
#10 0x0000557afc0d1aa2 in JOIN::choose_subquery_plan (this=0x7f06500167f8, join_tables=1) at /data/src/10.3/sql/opt_subselect.cc:5944
|
#11 0x0000557afbf4931f in make_join_statistics (join=0x7f06500167f8, tables_list=..., keyuse_array=0x7f0650016ae8) at /data/src/10.3/sql/sql_select.cc:5195
|
#12 0x0000557afbf3d946 in JOIN::optimize_inner (this=0x7f06500167f8) at /data/src/10.3/sql/sql_select.cc:1951
|
#13 0x0000557afbf3be6a in JOIN::optimize (this=0x7f06500167f8) at /data/src/10.3/sql/sql_select.cc:1497
|
#14 0x0000557afbeca341 in st_select_lex::optimize_unflattened_subqueries (this=0x7f06500053e0, const_only=false) at /data/src/10.3/sql/sql_lex.cc:4100
|
#15 0x0000557afc0cff64 in JOIN::optimize_unflattened_subqueries (this=0x7f06500139e0) at /data/src/10.3/sql/opt_subselect.cc:5333
|
#16 0x0000557afbf3f867 in JOIN::optimize_stage2 (this=0x7f06500139e0) at /data/src/10.3/sql/sql_select.cc:2501
|
#17 0x0000557afbf3da3d in JOIN::optimize_inner (this=0x7f06500139e0) at /data/src/10.3/sql/sql_select.cc:1977
|
#18 0x0000557afbf3be6a in JOIN::optimize (this=0x7f06500139e0) at /data/src/10.3/sql/sql_select.cc:1497
|
#19 0x0000557afbf45f5e in mysql_select (thd=0x7f0650000d90, tables=0x7f0650012cf8, wild_num=1, fields=..., conds=0x7f0650013778, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f06500139b8, unit=0x7f0650004c58, select_lex=0x7f06500053e0) at /data/src/10.3/sql/sql_select.cc:4301
|
#20 0x0000557afbf3761e in handle_select (thd=0x7f0650000d90, lex=0x7f0650004b98, result=0x7f06500139b8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:370
|
#21 0x0000557afbefdf64 in execute_sqlcom_select (thd=0x7f0650000d90, all_tables=0x7f0650012cf8) at /data/src/10.3/sql/sql_parse.cc:6286
|
#22 0x0000557afbef46cb in mysql_execute_command (thd=0x7f0650000d90) at /data/src/10.3/sql/sql_parse.cc:3812
|
#23 0x0000557afbf022ce in mysql_parse (thd=0x7f0650000d90, rawbuf=0x7f0650012ab8 "SELECT * FROM t1 WHERE f NOT IN ('qux', REPEAT('a',333))", length=56, parser_state=0x7f06676165c0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7810
|
#24 0x0000557afbeeea8f in dispatch_command (command=COM_QUERY, thd=0x7f0650000d90, packet=0x7f0650008f11 "SELECT * FROM t1 WHERE f NOT IN ('qux', REPEAT('a',333))", packet_length=56, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1847
|
#25 0x0000557afbeed42f in do_command (thd=0x7f0650000d90) at /data/src/10.3/sql/sql_parse.cc:1393
|
#26 0x0000557afc06ac44 in do_handle_one_connection (connect=0x557aff33da40) at /data/src/10.3/sql/sql_connect.cc:1403
|
#27 0x0000557afc06a9a0 in handle_one_connection (arg=0x557aff33da40) at /data/src/10.3/sql/sql_connect.cc:1308
|
#28 0x0000557afca30925 in pfs_spawn_thread (arg=0x557aff36e830) at /data/src/10.3/storage/perfschema/pfs.cc:1869
|
#29 0x00007f0671b3d609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#30 0x00007f0671717293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
No obvious problem on a non-debug build, but there is still some concern, as a similar problem in MDEV-23828 is accompanied by a wrong result on a non-debug build.
EXPLAIN also crashes on 10.3-10.4, so the plan below is from a non-debug build, I presume they would be the same with debug.
|
Plan on 10.3
|
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
|
2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
Warnings:
|
Note 1003 /* select#1 */ select `test`.`t1`.`f` AS `f` from `test`.`t1` where !<expr_cache><`test`.`t1`.`f`>(<in_optimizer>(`test`.`t1`.`f`,<exists>(/* select#2 */ select `tvc_0`.`_col_1` from (values ('qux'),(convert(repeat('a',333) using utf8))) `tvc_0` where trigcond(<cache>(`test`.`t1`.`f`) = `tvc_0`.`_col_1` or `tvc_0`.`_col_1` is null) having trigcond(`tvc_0`.`_col_1` is null))))
|
|
Plan on 10.5
|
explain extended SELECT * FROM t1 WHERE f NOT IN ('qux', REPEAT('a',333));
|
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
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
Warnings:
|
Note 1003 /* select#1 */ select `test`.`t1`.`f` AS `f` from `test`.`t1` where !<expr_cache><`test`.`t1`.`f`>(<in_optimizer>(`test`.`t1`.`f`,`test`.`t1`.`f` in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1` from (values ('qux'),(convert(repeat('a',333) using utf8))) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`f` in <temporary table> on distinct_key where `test`.`t1`.`f` = `<subquery2>`.`_col_1`))))
|
|