[MDEV-26553] NOT IN subquery construct crashing 10.1 and up Created: 2021-09-06  Updated: 2022-04-04  Resolved: 2022-03-22

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.48, 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.43, 10.3.34, 10.4.24, 10.5.15, 10.6.7, 10.7.3

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

The following works on MySQL 5.6 and MariaDB 10.0, but crashes later MariaDB releases:

DROP TABLE IF EXISTS t1;
 
  
CREATE TABLE `t1` (
 
`id` int PRIMARY KEY,
 
`i1` int,
 
`i2` int,
 
KEY (`i1`,`i2`)
 
) ENGINE=InnoDB;
 
 
 
SELECT id
 
  FROM t1
 
 WHERE t1.id NOT IN (
 
                      SELECT s1.id
 
                        FROM t1 s1
 
                       WHERE ( ( s1.i1 = 1 OR s1.i1 = 1 ) AND s1.i2 = 1 )
 
                          OR ( s1.i1 = 1 AND s1.i2 = NULL )
 
                    )
 
;

As the crash already happens in the optimizer stage it also crashes the server when trying to run "EXPLAIN SELECT"

This seems to be independent from any optimizer_switch settings as even with all individual switch settings set to "off" it still crashes.

MariaDB 10.6.4 gdb backtrace:

#0  0x00007f39c99e055b in kill () at ../sysdeps/unix/syscall-template.S:78
 
#1  0x0000556f4122c25e in handle_fatal_signal (sig=11) at ./sql/signal_handler.cc:356
 
#2  <signal handler called>
 
#3  Item_equal_iterator<List_iterator_fast, Item>::get_curr_field (this=<synthetic pointer>) at ./sql/item_cmpfunc.h:3488
 
#4  Item_equal::contains (this=this@entry=0x7f39741efa00, field=0x7f39741e4868) at ./sql/item_cmpfunc.cc:6775
 
#5  0x0000556f4124225f in Item_field::find_item_equal (this=0x7f3974014dc8, cond_equal=0x7f3974015af8) at ./sql/item.cc:6194
 
#6  0x0000556f4103d8cc in eliminate_item_equal (thd=0x7f3974000c58, cond=0x0, upper_levels=0x7f3974015af8, item_equal=0x7f39741edec0) at ./sql/sql_select.cc:16049
 
#7  0x0000556f4103e4bb in substitute_for_best_equal_field (thd=0x7f3974000c58, context_tab=0x1, cond=0x7f39741edec0, cond_equal=<optimized out>, table_join_idx=0x7f39741ee678, do_substitution=<optimized out>) at ./sql/sql_select.cc:16356
 
#8  0x0000556f4103e536 in substitute_for_best_equal_field (thd=0x7f3974000c58, context_tab=0x1, cond=0x7f3974015558, cond_equal=0x7f39741efda8, table_join_idx=<optimized out>, do_substitution=<optimized out>) at ./sql/sql_select.cc:16274
 
#9  0x0000556f4103e536 in substitute_for_best_equal_field (thd=0x7f3974000c58, context_tab=0x1, cond=0x7f39741efcd0, cond_equal=0x7f39741efda8, table_join_idx=<optimized out>, do_substitution=<optimized out>) at ./sql/sql_select.cc:16274
 
#10 0x0000556f41060f73 in JOIN::optimize_stage2 (this=0x7f3974018108) at ./sql/sql_select.cc:2599
 
#11 0x0000556f41064a4a in JOIN::optimize_inner (this=0x7f3974018108) at ./sql/sql_select.cc:2479
 
#12 0x0000556f41066ad3 in JOIN::optimize (this=this@entry=0x7f3974018108) at ./sql/sql_select.cc:1809
 
#13 0x0000556f40fd7466 in st_select_lex::optimize_unflattened_subqueries (this=0x7f39740132c8, const_only=const_only@entry=false) at ./sql/sql_lex.cc:4937
 
#14 0x0000556f41150ae2 in JOIN::optimize_unflattened_subqueries (this=this@entry=0x7f3974017988) at ./sql/opt_subselect.cc:5567
 
#15 0x0000556f410620b6 in JOIN::optimize_stage2 (this=<optimized out>) at ./sql/sql_select.cc:3061
 
#16 0x0000556f41064a4a in JOIN::optimize_inner (this=0x7f3974017988) at ./sql/sql_select.cc:2479
 
#17 0x0000556f41066ad3 in JOIN::optimize (this=this@entry=0x7f3974017988) at ./sql/sql_select.cc:1809
 
#18 0x0000556f41066b97 in mysql_select (thd=0x7f3974000c58, tables=0x7f39740138a8, fields=..., conds=0x7f3974016f58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x7f3974017960, unit=0x7f3974004e00, select_lex=0x7f39740132c8) at ./sql/sql_select.cc:4979
 
#19 0x0000556f410673b7 in handle_select (thd=thd@entry=0x7f3974000c58, lex=lex@entry=0x7f3974004d38, result=result@entry=0x7f3974017960, setup_tables_done_option=setup_tables_done_option@entry=0) at ./sql/sql_select.cc:545
 
#20 0x0000556f40ff7431 in execute_sqlcom_select (thd=0x7f3974000c58, all_tables=0x7f39740138a8) at ./sql/sql_parse.cc:6256
 
#21 0x0000556f4100516e in mysql_execute_command (thd=0x7f3974000c58, is_called_from_prepared_stmt=<optimized out>) at ./sql/sql_parse.cc:3946
 
#22 0x0000556f40ff2367 in mysql_parse (thd=0x7f3974000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at ./sql/sql_parse.cc:8030
 
#23 0x0000556f40ffe2ed in dispatch_command (command=COM_QUERY, thd=0x7f3974000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at ./sql/sql_class.h:1357
 
#24 0x0000556f41000208 in do_command (thd=0x7f3974000c58, blocking=blocking@entry=true) at ./sql/sql_parse.cc:1404
 
#25 0x0000556f4110f867 in do_handle_one_connection (connect=<optimized out>, put_in_cache=true) at ./sql/sql_connect.cc:1418
 
#26 0x0000556f4110fbbd in handle_one_connection (arg=arg@entry=0x556f4471ee08) at ./sql/sql_connect.cc:1312
 
#27 0x0000556f4146d17d in pfs_spawn_thread (arg=0x556f446a1188) at ./storage/perfschema/pfs.cc:2201
 
#28 0x00007f39c9ece609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#29 0x00007f39c9abc293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95



 Comments   
Comment by Alice Sherepa [ 2021-09-07 ]

Setting optimizer_switch to 'in_to_exists=off' prevents the crash.

The initial test case crashed with InnoDB engine, but not Myisam,
the test below repeats the crash with InnoDB/MyIsam/Aria on 10.2-10.6:

 
 
CREATE TABLE t1 (i1 int, i2 int, KEY (i1,i2));
 
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);  #not important
 
 
 
SELECT 1 FROM t1 s1
 
WHERE 1 NOT IN
 
  (SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 1) AND i2 = 1) OR i2 = NULL);
 
 
 
DROP TABLE t1;

10.2 38648bbbf53124a0d040471

 
#3  <signal handler called>
 
#4  0x00005556c4ad47f8 in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field (this=0x7f75f82b2b60) at /10.2/src/sql/item_cmpfunc.h:2515
 
#5  0x00005556c4d1bc45 in Item_equal::contains (this=0x7f75a00184e8, field=0x7f75a0036898) at /10.2/src/sql/item_cmpfunc.cc:6491
 
#6  0x00005556c4cede05 in Item_field::find_item_equal (this=0x7f75a0014270, cond_equal=0x7f75a0014d70) at /10.2/src/sql/item.cc:5766
 
#7  0x00005556c4aa8f27 in eliminate_item_equal (thd=0x7f75a0000d90, cond=0x0, upper_levels=0x7f75a0014d70, item_equal=0x7f75a0016c78) at /10.2/src/sql/sql_select.cc:14105
 
#8  0x00005556c4aa9b5d in substitute_for_best_equal_field (thd=0x7f75a0000d90, context_tab=0x1, cond=0x7f75a0016c78, cond_equal=0x7f75a0014d70, table_join_idx=0x7f75a0017368) at /10.2/src/sql/sql_select.cc:14405
 
#9  0x00005556c4aa984b in substitute_for_best_equal_field (thd=0x7f75a0000d90, context_tab=0x1, cond=0x7f75a0014878, cond_equal=0x7f75a009ec30, table_join_idx=0x7f75a0017368) at /10.2/src/sql/sql_select.cc:14330
 
#10 0x00005556c4aa984b in substitute_for_best_equal_field (thd=0x7f75a0000d90, context_tab=0x1, cond=0x7f75a009eb48, cond_equal=0x7f75a009ec30, table_join_idx=0x7f75a0017368) at /10.2/src/sql/sql_select.cc:14330
 
#11 0x00005556c4a87113 in JOIN::optimize_inner (this=0x7f75a0015c48) at /10.2/src/sql/sql_select.cc:1686
 
#12 0x00005556c4a84fe6 in JOIN::optimize (this=0x7f75a0015c48) at /10.2/src/sql/sql_select.cc:1127
 
#13 0x00005556c4a33c5f in st_select_lex::optimize_unflattened_subqueries (this=0x7f75a00050d8, const_only=true) at /10.2/src/sql/sql_lex.cc:3869
 
#14 0x00005556c4bf8b05 in JOIN::optimize_constant_subqueries (this=0x7f75a00155c8) at /10.2/src/sql/opt_subselect.cc:5360
 
#15 0x00005556c4a85b02 in JOIN::optimize_inner (this=0x7f75a00155c8) at /10.2/src/sql/sql_select.cc:1349
 
#16 0x00005556c4a84fe6 in JOIN::optimize (this=0x7f75a00155c8) at /10.2/src/sql/sql_select.cc:1127
 
#17 0x00005556c4a8e53c in mysql_select (thd=0x7f75a0000d90, tables=0x7f75a0012938, wild_num=0, fields=..., conds=0x7f75a00153f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f75a00155a8, unit=0x7f75a0004988, select_lex=0x7f75a00050d8) at /10.2/src/sql/sql_select.cc:3835
 
#18 0x00005556c4a82720 in handle_select (thd=0x7f75a0000d90, lex=0x7f75a00048c8, result=0x7f75a00155a8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
 
#19 0x00005556c4a4cd86 in execute_sqlcom_select (thd=0x7f75a0000d90, all_tables=0x7f75a0012938) at /10.2/src/sql/sql_parse.cc:6271
 
#20 0x00005556c4a438fa in mysql_execute_command (thd=0x7f75a0000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#21 0x00005556c4a50b42 in mysql_parse (thd=0x7f75a0000d90, rawbuf=0x7f75a0012708 "SELECT 1 FROM t1 s1\nWHERE 1 NOT IN\n(SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 1) AND i2 = 1) OR i2 = NULL)", length=104, parser_state=0x7f75f82b4560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#22 0x00005556c4a3ed9d in dispatch_command (command=COM_QUERY, thd=0x7f75a0000d90, packet=0x7f75a0008b61 "SELECT 1 FROM t1 s1\nWHERE 1 NOT IN\n(SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 1) AND i2 = 1) OR i2 = NULL)", packet_length=104, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#23 0x00005556c4a3d898 in do_command (thd=0x7f75a0000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#24 0x00005556c4b99661 in do_handle_one_connection (connect=0x5556c8257b80) at /10.2/src/sql/sql_connect.cc:1336
 
#25 0x00005556c4b993c6 in handle_one_connection (arg=0x5556c8257b80) at /10.2/src/sql/sql_connect.cc:1241
 
#26 0x00005556c53c2ee2 in pfs_spawn_thread (arg=0x5556c823ae40) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#27 0x00007f75fdc2f609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#28 0x00007f75fd80a293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Igor Babaev [ 2021-11-24 ]

A more natural variant using Standard SQL that uses the predicate (i2 IS NULL) works fine:

MariaDB [test]> SELECT 1 FROM t1 s1  WHERE 1 NOT IN    (SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 1) AND i2 = 1) OR i2 IS NULL);
 
Empty set (0.00 sec)

The fact is that the predicate (i2 = NULL) is always evaluated to NULL/FALSE and it does not make sense to use it as a disjunct at the top level of any WHERE condition.

Comment by Igor Babaev [ 2021-11-24 ]

The following query also works fine:

MariaDB [test]> SELECT 1 FROM t1 s1 WHERE 1 NOT IN   (SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 1) AND i2 = 1) OR 2=1);
 
Empty set (0.00 sec)

Here we also have a disjunct that is always FALSE that is removed from the top level disjunction. It is done by this code in the function optimize_cond()

 
     conds= conds->remove_eq_conds(thd, cond_value, true);
 
     if (conds && conds->type() == Item::COND_ITEM &&
 
         ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC)
 
       *cond_equal= &((Item_cond_and*) conds)->m_cond_equal;

We see that JOIN::cond_equal is updated here.

At the same time we see that for the query 

SELECT 1 FROM t1 s1  WHERE 1 NOT IN    (SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 1) AND i2 = 1) OR i2 = NULL);

the disjunct i2 is NULL is not removed when optimize_cond() is called for WHERE.
However it is removed when get_quick_record_count() is called. After this call we see that JOIN::conds is updated while JOIN::item_equal is not updated

         /* Range analyzer could modify the condition. */
 
         if (*s->on_expr_ref)
 
           *s->on_expr_ref= select->cond;
 
         else
 
           join->conds= select->cond;

The above code should have updated join->item_equal as well. As it was not done we have a crash later in substitute_for_best_equal_field ().

Comment by Igor Babaev [ 2021-11-25 ]

The disjunct (i2 = NULL) could have been removed by the call of remove_eq_conds(). Here is an example with the disjunct that cannot be removed the call of remove_eq_conds(), but is removed by the mentioned call of get_quick_record_count():

SELECT 1 FROM t1 s1 WHERE 1 NOT IN   (SELECT 1 FROM t1 WHERE ((i1 = 1 OR i1 = 2) AND i2 = 1) OR (i2 > 5 AND i2 < 1));

This query causes the same kind of crash as the query from the reported test case.

Comment by Oleksandr Byelkin [ 2021-11-26 ]

OK to push

Comment by Igor Babaev [ 2021-11-29 ]

The variant of the fix that should be applied to 10.3 and up

{diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index fe02e7b..d1f041b 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -5070,6 +5070,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 
       if (!s->const_keys.is_clear_all())
 
       {
 
         sargable_cond= get_sargable_cond(join, s->table);
 
+        bool is_sargable_cond_of_where= sargable_cond == &join->conds;
 
 
         select= make_select(s->table, found_const_table_map,
 
                            found_const_table_map,
 
@@ -5086,6 +5087,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 
           condition to where we got it from.
 
         */
 
         *sargable_cond= select->cond;
 
+        if (is_sargable_cond_of_where &&
 
+            join->conds && join->conds->type() == Item::COND_ITEM &&
 
+            ((Item_cond*) (join->conds))->functype() ==
 
+            Item_func::COND_AND_FUNC)
 
+          join->cond_equal= &((Item_cond_and*) (join->conds))->m_cond_equal;
 
 
         s->quick=select->quick;
 
         s->needed_reg=select->needed_reg;

Comment by Igor Babaev [ 2022-03-22 ]

A fix for this bug was pushed into 10.2

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