[MDEV-22846] Server crashes in handler_index_cond_check on SELECT Created: 2020-06-09  Updated: 2022-02-02  Resolved: 2022-01-10

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5, 10.6
Fix Version/s: 10.4.23, 10.5.14, 10.6.6, 10.7.2

Type: Bug Priority: Critical
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: not-10.2, not-10.3, regression

Issue Links:
Relates
relates to MDEV-21446 Assertion `!prebuilt->index->is_prima... Closed
relates to MDEV-27366 SIGSEGV in handler_index_cond_check o... In Review

 Description    

--source include/have_innodb.inc
 
set optimizer_switch='not_null_range_scan=on';
 
 
 
CREATE TABLE t1 (pk int NOT NULL,c1 varchar(1));
 
INSERT INTO t1 VALUES (15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'),(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,NULL),(129,NULL),(133,NULL);
 
 
 
CREATE TABLE t2 (i1 int, c1 varchar(1), KEY c1 (c1), KEY i1 (i1)) engine=innodb;
 
INSERT INTO t2   VALUES (NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,'4'),(NULL,'4'),(NULL,NULL),(NULL,NULL);
 
 
 
SELECT 1 FROM t1 
WHERE t1.c1 NOT IN (SELECT t2.c1 FROM (t2 JOIN t1 AS a1 ON 1) 
WHERE t2.i1 = t1.pk);

10.5 70d4e55db94b62aa6cf

 
#3  <signal handler called>
 
#4  handler_index_cond_check (h_arg=0x7f18f8061450) at /10.5/sql/handler.cc:6188
 
#5  0x0000555ad17ca340 in row_search_idx_cond_check (mysql_rec=0x7f18f80583a8 "\377", prebuilt=0x7f18f8062808, rec=0x7f192f10007e "", offsets=0x7f19340d01f0) at /10.5/storage/innobase/row/row0sel.cc:3931
 
#6  0x0000555ad17ce3a3 in row_search_mvcc (buf=0x7f18f80583a8 "\377", mode=PAGE_CUR_G, prebuilt=0x7f18f8062808, match_mode=0, direction=0) at /10.5/storage/innobase/row/row0sel.cc:5207
 
#7  0x0000555ad15da15e in ha_innobase::index_read (this=0x7f18f8061450, buf=0x7f18f80583a8 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /10.5/storage/innobase/handler/ha_innodb.cc:8960
 
#8  0x0000555ad15db16c in ha_innobase::index_first (this=0x7f18f8061450, buf=0x7f18f80583a8 "\377") at /10.5/storage/innobase/handler/ha_innodb.cc:9334
 
#9  0x0000555ad15db344 in ha_innobase::rnd_next (this=0x7f18f8061450, buf=0x7f18f80583a8 "\377") at /10.5/storage/innobase/handler/ha_innodb.cc:9427
 
#10 0x0000555ad117f894 in handler::ha_rnd_next (this=0x7f18f8061450, buf=0x7f18f80583a8 "\377") at /10.5/sql/handler.cc:2989
 
#11 0x0000555ad1333c45 in rr_sequential (info=0x7f18f806da28) at /10.5/sql/records.cc:511
 
#12 0x0000555ad0d750a9 in READ_RECORD::read_record (this=0x7f18f806da28) at /10.5/sql/records.h:79
 
#13 0x0000555ad0eab4c9 in read_first_record_seq (tab=0x7f18f806d960) at /10.5/sql/sql_select.cc:21504
 
#14 0x0000555ad0ea949d in sub_select (join=0x7f18f8018980, join_tab=0x7f18f806d960, end_of_records=false) at /10.5/sql/sql_select.cc:20626
 
#15 0x0000555ad0ea895e in do_select (join=0x7f18f8018980, procedure=0x0) at /10.5/sql/sql_select.cc:20163
 
#16 0x0000555ad0e7ccab in JOIN::exec_inner (this=0x7f18f8018980) at /10.5/sql/sql_select.cc:4475
 
#17 0x0000555ad0e7bdd7 in JOIN::exec (this=0x7f18f8018980) at /10.5/sql/sql_select.cc:4256
 
#18 0x0000555ad12692ea in subselect_single_select_engine::exec (this=0x7f18f8017868) at /10.5/sql/item_subselect.cc:3939
 
#19 0x0000555ad125c729 in Item_subselect::exec (this=0x7f18f8017628) at /10.5/sql/item_subselect.cc:748
 
#20 0x0000555ad125cdcb in Item_in_subselect::exec (this=0x7f18f8017628) at /10.5/sql/item_subselect.cc:928
 
#21 0x0000555ad12607d2 in Item_in_subselect::val_bool (this=0x7f18f8017628) at /10.5/sql/item_subselect.cc:1806
 
#22 0x0000555ad0d00a9f in Item::val_bool_result (this=0x7f18f8017628) at /10.5/sql/item.h:1566
 
#23 0x0000555ad11c6bbd in Item_in_optimizer::val_int (this=0x7f18f8019010) at /10.5/sql/item_cmpfunc.cc:1639
 
#24 0x0000555ad0d00a13 in Item::val_int_result (this=0x7f18f8019010) at /10.5/sql/item.h:1562
 
#25 0x0000555ad11b5b17 in Item_cache_int::cache_value (this=0x7f18f806fac0) at /10.5/sql/item.cc:9772
 
#26 0x0000555ad11bde2c in Item_cache_wrapper::cache (this=0x7f18f806f9f8) at /10.5/sql/item.cc:8616
 
#27 0x0000555ad11b2c66 in Item_cache_wrapper::val_bool (this=0x7f18f806f9f8) at /10.5/sql/item.cc:8802
 
#28 0x0000555ad11c1d3b in Item_func_not::val_int (this=0x7f18f80178a8) at /10.5/sql/item_cmpfunc.cc:202
 
#29 0x0000555ad0ea98fa in evaluate_join_record (join=0x7f18f80182d0, join_tab=0x7f18f8067ba0, error=0) at /10.5/sql/sql_select.cc:20727
 
#30 0x0000555ad0ea96c0 in sub_select (join=0x7f18f80182d0, join_tab=0x7f18f8067ba0, end_of_records=false) at /10.5/sql/sql_select.cc:20668
 
#31 0x0000555ad0ea895e in do_select (join=0x7f18f80182d0, procedure=0x0) at /10.5/sql/sql_select.cc:20163
 
#32 0x0000555ad0e7ccab in JOIN::exec_inner (this=0x7f18f80182d0) at /10.5/sql/sql_select.cc:4475
 
#33 0x0000555ad0e7bdd7 in JOIN::exec (this=0x7f18f80182d0) at /10.5/sql/sql_select.cc:4256
 
#34 0x0000555ad0e7d4f8 in mysql_select (thd=0x7f18f8000d78, tables=0x7f18f8014468, fields=..., conds=0x7f18f80178a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f18f80182a8, unit=0x7f18f8004d90, select_lex=0x7f18f8013f10) at /10.5/sql/sql_select.cc:4680
 
#35 0x0000555ad0e6d164 in handle_select (thd=0x7f18f8000d78, lex=0x7f18f8004cc8, result=0x7f18f80182a8, setup_tables_done_option=0) at /10.5/sql/sql_select.cc:417
 
#36 0x0000555ad0e32c60 in execute_sqlcom_select (thd=0x7f18f8000d78, all_tables=0x7f18f8014468) at /10.5/sql/sql_parse.cc:6208
 
#37 0x0000555ad0e2a019 in mysql_execute_command (thd=0x7f18f8000d78) at /10.5/sql/sql_parse.cc:3939
 
#38 0x0000555ad0e37aa1 in mysql_parse (thd=0x7f18f8000d78, rawbuf=0x7f18f8013de0 "SELECT 1 FROM t1 \nWHERE t1.c1 NOT IN (SELECT t2.c1 FROM (t2 JOIN t1 AS a1 ON 1) \nWHERE t2.i1 = t1.pk)", length=101, parser_state=0x7f19340d2510, is_com_multi=false, is_next_command=false) at /10.5/sql/sql_parse.cc:7992
 
#39 0x0000555ad0e23f6e in dispatch_command (command=COM_QUERY, thd=0x7f18f8000d78, packet=0x7f18f8008f59 "", packet_length=101, is_com_multi=false, is_next_command=false) at /10.5/sql/sql_parse.cc:1874
 
#40 0x0000555ad0e226ae in do_command (thd=0x7f18f8000d78) at /10.5/sql/sql_parse.cc:1355
 
#41 0x0000555ad0fc4bc0 in do_handle_one_connection (connect=0x555ad4433168, put_in_cache=true) at /10.5/sql/sql_connect.cc:1411
 
#42 0x0000555ad0fc4929 in handle_one_connection (arg=0x555ad44d9028) at /10.5/sql/sql_connect.cc:1313
 
#43 0x0000555ad14f778b in pfs_spawn_thread (arg=0x555ad45164d8) at /10.5/storage/perfschema/pfs.cc:2201
 
#44 0x00007f193a3f5fa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
 
#45 0x00007f19399fe4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

also on non-debug:

Version: '10.5.4-MariaDB-debug-log'  
200609 15:43:18 [ERROR] mysqld got signal 11 ;
 
 
 
mysys/stacktrace.c:306(my_print_stacktrace)[0x555ad1175503]
 
??:0(__restore_rt)[0x7f193a400730]
 
sql/handler.cc:6188(handler_index_cond_check)[0x555ad17ca340]
 
row/row0sel.cc:3931(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned short const*))[0x555ad17ce3a3]
 
row/row0sel.cc:5207(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x555ad15da15e]
 
handler/ha_innodb.cc:8960(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x555ad15db16c]
 
handler/ha_innodb.cc:9334(ha_innobase::index_first(unsigned char*))[0x555ad15db344]
 
handler/ha_innodb.cc:9427(ha_innobase::rnd_next(unsigned char*))[0x555ad117f894]
 
sql/handler.cc:2989(handler::ha_rnd_next(unsigned char*))[0x555ad1333c45]
 
sql/records.cc:511(rr_sequential(READ_RECORD*))[0x555ad0d750a9]
 
sql/records.h:79(READ_RECORD::read_record())[0x555ad0ea949d]
 
sql/sql_select.cc:21504(read_first_record_seq(st_join_table*))[0x555ad0ea895e]
 
sql/sql_select.cc:20626(sub_select(JOIN*, st_join_table*, bool))[0x555ad0e7ccab]
 
sql/sql_select.cc:20163(do_select(JOIN*, Procedure*))[0x555ad0e7bdd7]
 
sql/sql_select.cc:4475(JOIN::exec_inner())[0x555ad12692ea]
 
sql/item_subselect.cc:3942(subselect_single_select_engine::exec())[0x555ad125c729]
 
sql/item_subselect.cc:748(Item_subselect::exec())[0x555ad125cdcb]
 
sql/item_subselect.cc:928(Item_in_subselect::exec())[0x555ad12607d2]
 
sql/item_subselect.cc:1806(Item_in_subselect::val_bool())[0x555ad0d00a9f]
 
sql/item.h:1566(Item::val_bool_result())[0x555ad11b2c66]
 
sql/item_cmpfunc.cc:1640(Item_in_optimizer::val_int())[0x555ad11c1d3b]
 
sql/item.h:1562(Item::val_int_result())[0x555ad0ea98fa]
 
sql/item.cc:9772(Item_cache_int::cache_value())[0x555ad0ea96c0]
 
sql/item.cc:8617(Item_cache_wrapper::cache())[0x555ad0ea895e]
 
sql/item.cc:8803(Item_cache_wrapper::val_bool())[0x555ad0e7ccab]
 
sql/item_cmpfunc.cc:202(Item_func_not::val_int())[0x555ad0e7bdd7]
 
sql/sql_select.cc:20727(evaluate_join_record(JOIN*, st_join_table*, int))[0x555ad0e7d4f8]
 
sql/sql_select.cc:20668(sub_select(JOIN*, st_join_table*, bool))[0x555ad0e6d164]
 
sql/sql_select.cc:20163(do_select(JOIN*, Procedure*))[0x555ad0e32c60]
 
sql/sql_select.cc:4475(JOIN::exec_inner())[0x555ad0e2a019]
 
sql/sql_select.cc:4257(JOIN::exec())[0x555ad0e37aa1]
 
sql/sql_select.cc:4682(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x555ad0e23f6e]
 
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x555ad0e226ae]
 
sql/sql_parse.cc:6208(execute_sqlcom_select(THD*, TABLE_LIST*))[0x555ad0fc4bc0]
 
sql/sql_parse.cc:3939(mysql_execute_command(THD*))[0x555ad0fc4929]
 
sql/sql_parse.cc:7992(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555ad14f778b]
 
nptl/pthread_create.c:487(start_thread)[0x7f193a3f5fa3]
 
x86_64/clone.S:97(clone)[0x7f19399fe4cf]
 
 
 
 
 
Query (0x7f18f8013de0): SELECT 1 FROM t1  WHERE t1.c1 NOT IN (SELECT t2.c1 FROM (t2 JOIN t1 AS a1 ON 1)  WHERE t2.i1 = t1.pk)



 Comments   
Comment by Igor Babaev [ 2020-06-11 ]

Observations:

If to set the 'not_null_range_scan' optimizer switch to 'off' and add the condition t2.i1 IS NOT NULL
to the WHERE clause of the subquery

--source include/have_innodb.inc
 
 
 
set optimizer_switch='not_null_range_scan=off';
 
 
CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb;
 
INSERT INTO t1 VALUES
 
(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'),(53,'l'),
 
(62,'a'),(71,NULL),(79,'u'),(128,NULL),(129,NULL),(133,NULL);
 
 
CREATE TABLE t2 (i1 int, c1 varchar(1), KEY c1 (c1), KEY i1 (i1)) engine=innodb;
 
 
 
INSERT INTO t2 VALUES
 
(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),
 
(NULL,'4'),(NULL,'4'),(NULL,NULL),(NULL,NULL);
 
 
SELECT 1 FROM t1 
WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
 
                      WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
 
 
 
DROP TABLE t1,t2;

the crash still can be reproduced in 10.5.

Now the output of EXPLAIN for the query looks like this:

EXPLAIN EXTENDED
 
SELECT 1 FROM t1 
WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
 
WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
 
id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
1      PRIMARY t1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using where
 
2      DEPENDENT SUBQUERY      t2      ref_or_null|filter      c1,i1   c1|i1   4|5     func    2 (10%) 10.00   Using
 
where; Full scan on NULL key; Using rowid filter
 
2      DEPENDENT SUBQUERY      a1      ALL     NULL    NULL    NULL    NULL    14      100.00  Using join buffer (flat, BNL join)

Comment by Igor Babaev [ 2020-06-11 ]

Let's slightly change table t2 making column t2.c1 not nullable and change rows in the tables:

--source include/have_innodb.inc
 
 CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb;
 
INSERT INTO t1 VALUES
 
(1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'),
 
(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL);
 
 
CREATE TABLE t2 (i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1)) engine=innodb;
 
 
INSERT INTO t2 VALUES
 
(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'),
 
(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w');
 
INSERT INTO t2 SELECT * FROM t2;
 
 
 
SELECT * FROM t1 
WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
 
                      WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);

The new test case crashes with the same stack, but in EXPLAIN output shows that t2 is accessed now by ref method rather by ref_or_null.

EXPLAIN EXTENDED
 
SELECT 1 FROM t1 
WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 
WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
 
id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
1      PRIMARY t1      ALL     NULL    NULL    NULL    NULL    15      100.00  Using where
 
2      DEPENDENT SUBQUERY      t2      ref|filter      c1,i1   c1|i1   3|5     func    6 (33%) 33.33   Using where; Full scan on NULL key; Using rowid filter
 
2      DEPENDENT SUBQUERY      a1      ALL     NULL    NULL    NULL    NULL    15      100.00  Using join buffer (flat, BNL join)

Comment by Igor Babaev [ 2020-06-11 ]

Running a build with Valgrind shows that there are some memory deallocation problems.
The crash handler_index_cond_check should not had happened as the execution plan does not display any index condition pushdown.

Comment by Igor Babaev [ 2020-06-12 ]

When running the above test on 10.4 Valgrind reports problems similar to those on 10.5.
The sever crashes with the same stack as on 10.5

Comment by Igor Babaev [ 2020-06-12 ]

If to set the optimizer switch 'rowid_filter' to 'off' the above test works fine on 10.4 and 10.5 with Valgrind build and with regular builds.

If to change the tables for MyiSAM tables the test works fine on 10.4 and 10.5 with Valgrind build and with regular builds.

The most probable cause of the problem is that pushdown of an rowid filter into InnoDB engine does not work well with such modification of ref access when for keys containing NULL values a full table scan of the joined table is employed. Such modification is used for a NOT IN subquery if IN to EXISTS transformation is applied to it.

Comment by Oleksandr Byelkin [ 2021-12-28 ]

OK to push

Comment by Igor Babaev [ 2022-01-10 ]

A fix for this bug was pushed into 10.4. It should be merged upstream as it is.

Generated at Thu Feb 08 09:17:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.