From af2ab040ccf9b8814052b3253900c7bc5f6ed58b Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 30 Mar 2005 12:14:37 +0200 Subject: BUG#8807 Select crash server - Add function Item_param::fix_fields which will update any subselect they are part of and indicate that the subsleect is not const during prepare phase, and thus should not be executed during prepare. mysql-test/include/ps_query.inc: Adde new test case mysql-test/r/ps_2myisam.result: Update test result mysql-test/r/ps_3innodb.result: Update test result mysql-test/r/ps_4heap.result: Update test result mysql-test/r/ps_5merge.result: Update test result mysql-test/r/ps_6bdb.result: Update test result mysql-test/r/ps_7ndb.result: Update test result sql/item.cc: Add function Item_param::fix_fields, which will mark any subselects they are part of as not being a constant expression unless the param value is specified, ie. it will be not be constant during prepare phase. sql/item.h: Adde Item_param::fix_fields sql/item_subselect.h: Make Item_param::fix_field friend of Item_subselect --- mysql-test/include/ps_query.inc | 5 +++++ mysql-test/r/ps_2myisam.result | 4 ++++ mysql-test/r/ps_3innodb.result | 4 ++++ mysql-test/r/ps_4heap.result | 4 ++++ mysql-test/r/ps_5merge.result | 8 ++++++++ mysql-test/r/ps_6bdb.result | 4 ++++ mysql-test/r/ps_7ndb.result | 4 ++++ sql/item.cc | 28 ++++++++++++++++++++++++++++ sql/item.h | 1 + sql/item_subselect.h | 1 + 10 files changed, 63 insertions(+) diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc index e6f19c67138..9a413bff2f3 100644 --- a/mysql-test/include/ps_query.inc +++ b/mysql-test/include/ps_query.inc @@ -421,6 +421,11 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where b=? and a = (select ? from t1 where b = ? ) ' ; execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; +# Bug#8807 +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; + ######## correlated subquery # no parameter prepare stmt1 from ' select a, b FROM t1 outer_table where diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index a27b8b996cd..a983ecd187d 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -768,6 +768,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index eb17d25e80c..61ec956035d 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -768,6 +768,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index b53ad7e2409..7f3bd0547ef 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -769,6 +769,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 13227eb533b..4682c70661c 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -811,6 +811,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; @@ -3821,6 +3825,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index 8630ba2db9b..01beb7924a0 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -768,6 +768,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result index 43ff9607c55..9b988a46f1a 100644 --- a/mysql-test/r/ps_7ndb.result +++ b/mysql-test/r/ps_7ndb.result @@ -768,6 +768,10 @@ prepare stmt1 from ' select a, ?, b FROM t1 outer_table where execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; a ? b 2 1 two +prepare stmt1 from 'select c4 FROM t9 where + c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; +execute stmt1 using @arg01, @arg02; +c4 prepare stmt1 from ' select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) order by a '; execute stmt1 ; diff --git a/sql/item.cc b/sql/item.cc index 2250bd9b32c..ba495cef03e 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1319,6 +1319,34 @@ bool Item_param::convert_str_value(THD *thd) return rc; } +bool Item_param::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) +{ + DBUG_ASSERT(fixed == 0); + SELECT_LEX *cursel= (SELECT_LEX *) thd->lex->current_select; + + /* + Parameters in a subselect should mark the subselect as not constant + during prepare + */ + if (state == NO_VALUE) + { + /* + SELECT_LEX_UNIT::item set only for subqueries, so test of it presence + can be barrier to stop before derived table SELECT or very outer SELECT + */ + for(; + cursel->master_unit()->item; + cursel= cursel->outer_select()) + { + Item_subselect *subselect_item= cursel->master_unit()->item; + subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT; + subselect_item->const_item_cache= 0; + } + } + fixed= 1; + return 0; +} + /* End of Item_param related */ diff --git a/sql/item.h b/sql/item.h index a90bb184449..969a0cc52d0 100644 --- a/sql/item.h +++ b/sql/item.h @@ -559,6 +559,7 @@ public: bool get_time(TIME *tm); bool get_date(TIME *tm, uint fuzzydate); int save_in_field(Field *field, bool no_conversions); + bool fix_fields(THD *, struct st_table_list *, Item **); void set_null(); void set_int(longlong i, uint32 max_length_arg); diff --git a/sql/item_subselect.h b/sql/item_subselect.h index ab2d441ed7a..02bb77ce643 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -119,6 +119,7 @@ public: friend class Item_in_optimizer; friend bool Item_field::fix_fields(THD *, TABLE_LIST *, Item **); friend bool Item_ref::fix_fields(THD *, TABLE_LIST *, Item **); + friend bool Item_param::fix_fields(THD *, TABLE_LIST *, Item **); }; /* single value subselect */ -- cgit v1.2.1