Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8902

Row IN subquery with semi-join and PS re-execution

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0.21, 10.1.7, 5.5.45
        • Fix Version/s: 10.1, 10.0, 5.5
        • Component/s: Optimizer
        • Labels:
          None

          Description

          CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
          INSERT INTO t1 VALUES (3),(9);

          CREATE TABLE t2 (column2 INT, column2_2 INT) ENGINE=MyISAM;

          INSERT INTO t2 VALUES (1,1),(4,4);

          CREATE TABLE t3 (column3 INT, column3_2 INT) ENGINE=MyISAM;
          INSERT INTO t3 VALUES (6, 6),(8, 8);

          CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
          INSERT INTO t4 VALUES (2),(5);

          PREPARE stmt FROM "
          SELECT (
          SELECT MAX( table1.column1 ) AS field1
          FROM t1 AS table1
          WHERE (table3.column3, table3.column3_2) IN ( SELECT table2.column2, table2.column2_2 AS field2 FROM t2 AS table2 )
          ) AS sq
          FROM t3 AS table3, t4 AS table4 GROUP BY sq
          ";

          EXECUTE stmt;
          EXECUTE stmt;

          deallocate prepare stmt;
          drop table t1,t2,t3,t4;

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-7846 Server crashes in Item_subselect::fix_fields or fails with Thread stack overrun

                • Critical - Crashes, loss of data, severe memory leak.
                • In Review

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  The problem is that converting to semi-join makes reference on temporary Items.

                  Solution is to make reference via row object which was disassembled using Item_ref inherited object:

                  diff --git a/sql/item.h b/sql/item.h
index f8e8ead..a8eb595 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2948,7 +2948,8 @@ class Item_ref :public Item_ident
   void set_properties();
   bool set_properties_only; // the item doesn't need full fix_fields
 public:
-  enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF };
+  enum Ref_Type
+  { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF, TRANS_REF };
   Field *result_field;			 /* Save result here */
   Item **ref;
   bool reference_trough_name;
@@ -3144,6 +3145,26 @@ class Item_direct_ref :public Item_ref
   virtual Ref_Type ref_type() { return DIRECT_REF; }
 };
 
+/*
+  Like direct_ref but use permanent reference and calls fix_fields on it
+*/
+class Item_direct_transparent_ref :public Item_direct_ref
+{
+public:
+  Item_direct_transparent_ref(Item **ref)
+    :Item_direct_ref(NULL, ref, "<NO_REF_TABLE>", (*ref)->name, FALSE)
+  {}
+
+  bool fix_fields(THD *thd, Item **it)
+  {
+    if ((!(*ref)->fixed && (*ref)->fix_fields(thd, ref)) ||
+        (*ref)->check_cols(1))
+      return TRUE;
+    set_properties();
+    return FALSE;
+  }
+  virtual Ref_Type ref_type() { return TRANS_REF; }
+};
 
 /**
   This class is the same as Item_direct_ref but created to wrap Item_ident
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 1363be0..286047c 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1605,8 +1605,9 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
     {
       nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr->
                                                 element_index(i));
-      Item_func_eq *item_eq= 
-        new Item_func_eq(subq_pred->left_expr->element_index(i), 
+      Item_func_eq *item_eq=
+        new Item_func_eq(new Item_direct_transparent_ref(
+                             subq_pred->left_expr->addr(i)),
                          subq_lex->ref_pointer_array[i]);
       item_eq->in_equality_no= i;
       sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e542b08..0d25167 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8219,7 +8219,8 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
 	    ((((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF &&
               (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() ==
               Item_ref::DIRECT_REF) || 
-             ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF) &&
+             ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF ||
+             ((Item_ref*)keyuse->val)->ref_type() == Item_ref::TRANS_REF) &&
             keyuse->val->real_item()->type() == Item::FIELD_ITEM))
     return new store_key_field(thd,
 			       key_part->field,

                  but dif above makes crashes in other tests

                  Show
                  sanja Oleksandr Byelkin added a comment - The problem is that converting to semi-join makes reference on temporary Items. Solution is to make reference via row object which was disassembled using Item_ref inherited object: diff --git a/sql/item.h b/sql/item.h index f8e8ead..a8eb595 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2948,7 +2948,8 @@ class Item_ref : public Item_ident void set_properties(); bool set_properties_only; // the item doesn't need full fix_fields public : - enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF }; + enum Ref_Type + { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF, TRANS_REF }; Field *result_field; /* Save result here */ Item **ref; bool reference_trough_name; @@ -3144,6 +3145,26 @@ class Item_direct_ref : public Item_ref virtual Ref_Type ref_type() { return DIRECT_REF; } }; +/* + Like direct_ref but use permanent reference and calls fix_fields on it +*/ +class Item_direct_transparent_ref : public Item_direct_ref +{ + public : + Item_direct_transparent_ref(Item **ref) + :Item_direct_ref(NULL, ref, "<NO_REF_TABLE>" , (*ref)->name, FALSE) + {} + + bool fix_fields(THD *thd, Item **it) + { + if ((!(*ref)->fixed && (*ref)->fix_fields(thd, ref)) || + (*ref)->check_cols(1)) + return TRUE; + set_properties(); + return FALSE; + } + virtual Ref_Type ref_type() { return TRANS_REF; } +}; /** This class is the same as Item_direct_ref but created to wrap Item_ident diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 1363be0..286047c 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1605,8 +1605,9 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) { nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr-> element_index(i)); - Item_func_eq *item_eq= - new Item_func_eq(subq_pred->left_expr->element_index(i), + Item_func_eq *item_eq= + new Item_func_eq( new Item_direct_transparent_ref( + subq_pred->left_expr->addr(i)), subq_lex->ref_pointer_array[i]); item_eq->in_equality_no= i; sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e542b08..0d25167 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8219,7 +8219,8 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables, ((((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF && (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() == Item_ref::DIRECT_REF) || - ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF) && + ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF || + ((Item_ref*)keyuse->val)->ref_type() == Item_ref::TRANS_REF) && keyuse->val->real_item()->type() == Item::FIELD_ITEM)) return new store_key_field(thd, key_part->field, but dif above makes crashes in other tests
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  The crash is repeatable in MySQL 5.7

                  Show
                  sanja Oleksandr Byelkin added a comment - The crash is repeatable in MySQL 5.7

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      sanja Oleksandr Byelkin
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated: