[MDEV-16839] Upper Levels not set for Item_equal objects Created: 2018-07-27  Updated: 2023-11-10

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Here is the test case , where we see for Item_equal objects we don't see field upper_levels set

CREATE TABLE t1(a INT, b INT, c INT);
 
INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2);
 
SELECT * FROM t1
 
WHERE (t1.b=13) AND (((t1.c=1) AND (t1.a=t1.c)) OR (t1.a=2));



 Comments   
Comment by Varun Gupta (Inactive) [ 2018-07-27 ]

A patch proposed was

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index af6f63a55f1..56d09e8c7e0 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -12105,6 +12105,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond,
 
         item_equal->update_used_tables();
 
         set_if_bigger(thd->lex->current_select->max_equal_elems,
 
                       item_equal->n_field_items());  
+        item_equal->upper_levels= inherited;
 
       }
 
 
       ((Item_cond_and*)cond)->cond_equal.copy(cond_equal);
 
@@ -12186,6 +12187,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond,
 
           item_equal->update_used_tables();
 
           set_if_bigger(thd->lex->current_select->max_equal_elems,
 
                         item_equal->n_field_items());  
+          item_equal->upper_levels= inherited;
 
         }
 
         and_cond->cond_equal.copy(cond_equal);
 
         cond_equal.current_level= and_cond->cond_equal.current_level;

Comment by Varun Gupta (Inactive) [ 2018-07-27 ]

Review comments from igor

1. Multiple equality multiple equal(1, `test`.`t1`.`c`, `test`.`t1`.`a`) does not inherit anything from the upper level (we have there only multiple equal(13, `test`.`t1`.`b`)).
The inheritance reference is used when we convert from a multiple equality back to a conjunction of equalities. Here must to check that an equality we produce from the multiple equality is not inherited from the upper levels.

Here is another probable problem with your fix:
Suppose you have 3 AND levels (each next AND level goes through an OR level) with one equality at the top level a=b and an equality at the last level b=c. As a result we have at the last level the multiple equality (a,b,c) for which we have to know that it is inherited from the top levels and a=b should not be produced for the last level.

2. With your test case I never hit the second chunk (change) of your patch. In general it's not good.

Comment by Galina Shalygina (Inactive) [ 2018-08-02 ]

The problem occurs in build_equal_items_for_cond() method when "((t1.c=1) AND (t1.a=t1.c))" is processed. This equalities can be merged in one multiple equality "multiple equal(1, `test`.`t1`.`c`, `test`.`t1`.`a`)". The type of this condition obviously should be MULT_EQ_FUNC. Nevertheless after the transformation of the AND condition into the multiple equality this condition remains COND_ITEM type that consists of one element.

(gdb) p dbug_print_item(cond)
 
$17 = 0x555556888b40 <dbug_item_print_buf> "((`test`.`t1`.`c` = 1) and (`test`.`t1`.`a` = `test`.`t1`.`c`))"
 
(gdb) p dbug_print_item(cond)
 
$11 = 0x555556888b40 <dbug_item_print_buf> "(multiple equal(1, `test`.`t1`.`c`, `test`.`t1`.`a`))"
 
(gdb) p cond->type()
 
$12 = Item::COND_ITEM
 
(gdb) p ((Item_cond *)cond)->functype()
 
$13 = Item_func::COND_AND_FUNC

Only after the remove_eq_conds() call it becomes MULT_EQ_FUNC element but upper_levels link is not set. For the multiple equality that was got from (t1.a=2) it is set. Both multiple equalities of this OR-level should have the same upper_levels field.

(gdb) p dbug_print_item((Item_equal *)(((Item_cond *)(((Item_cond *)conds)->argument_list()->first->info))->argument_list()->first->info))
 
$39 = 0x555556888b40 <dbug_item_print_buf> "multiple equal(1, `test`.`t1`.`c`, `test`.`t1`.`a`)"
 
(gdb) p ((Item_equal *)(((Item_cond *)(((Item_cond *)conds)->argument_list()->first->info))->argument_list()->first->info))->upper_levels
 
$38 = (COND_EQUAL *) 0x0
 
(gdb) p dbug_print_item((Item_equal *)(((Item_cond *)(((Item_cond *)conds)->argument_list()->first->info))->argument_list()->first->next->info))
 
$40 = 0x555556888b40 <dbug_item_print_buf> "multiple equal(2, `test`.`t1`.`a`)"
 
(gdb) p ((Item_equal *)(((Item_cond *)(((Item_cond *)conds)->argument_list()->first->info))->argument_list()->first->next->info))->upper_levels
 
$41 = (COND_EQUAL *) 0x7fffac006950

The bug appears because build_equal_items_for_cond() doesn't process the case when AND-condition is transformed into the single multiple equality item in the right way. To fix it build_equal_items_for_cond() should be changed.

Comment by Igor Babaev [ 2019-05-02 ]

Galina,
See my review in your email box.

Generated at Thu Feb 08 08:31:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.