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

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

Details

    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;

      Attachments

        Issue Links

          Activity

            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

            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

            The crash is repeatable in MySQL 5.7

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

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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