[MDEV-26553] NOT IN subquery construct crashing 10.1 and up - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.48, 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4, 10.2(EOL), 10.3(EOL), 10.4(EOL), 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
Component/s: Optimizer
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

Attachments

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2021-09-07 08:30

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

Alice Sherepa added a comment - 2021-09-07 08:30 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

Igor Babaev (Inactive) added a comment - 2021-11-24 05:15 - edited

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.

Igor Babaev (Inactive) added a comment - 2021-11-24 05:15 - edited 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.

Igor Babaev (Inactive) added a comment - 2021-11-24 05:51 - edited

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 ().

Igor Babaev (Inactive) added a comment - 2021-11-24 05:51 - edited 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 ().

Igor Babaev (Inactive) added a comment - 2021-11-25 05:22

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.

Igor Babaev (Inactive) added a comment - 2021-11-25 05:22 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.

Oleksandr Byelkin added a comment - 2021-11-26 10:44

OK to push

Oleksandr Byelkin added a comment - 2021-11-26 10:44 OK to push

Igor Babaev (Inactive) added a comment - 2021-11-29 17:02

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;

Igor Babaev (Inactive) added a comment - 2021-11-29 17:02 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;

Igor Babaev (Inactive) added a comment - 2022-03-22 06:07

A fix for this bug was pushed into 10.2

Igor Babaev (Inactive) added a comment - 2022-03-22 06:07 A fix for this bug was pushed into 10.2

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Hartmut Holzgraefe

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2021-09-06 21:28

Updated:: 2024-07-07 21:03

Resolved:: 2022-03-22 06:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server