summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/ps.result57
-rw-r--r--mysql-test/t/ps.test68
-rw-r--r--sql/item.cc29
-rw-r--r--sql/item.h1
-rw-r--r--sql/item_subselect.h1
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/sql_lex.h1
-rw-r--r--sql/sql_parse.cc8
-rw-r--r--sql/sql_prepare.cc13
9 files changed, 146 insertions, 34 deletions
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index ca38f1c75cb..b1b06dc4019 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -563,3 +563,60 @@ execute stmt;
execute stmt;
deallocate prepare stmt;
drop table t1;
+create table t1 (
+id int(11) unsigned not null primary key auto_increment,
+partner_id varchar(35) not null,
+t1_status_id int(10) unsigned
+);
+insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
+("3", "partner3", "10"), ("4", "partner4", "10");
+create table t2 (
+id int(11) unsigned not null default '0',
+t1_line_id int(11) unsigned not null default '0',
+article_id varchar(20),
+sequence int(11) not null default '0',
+primary key (id,t1_line_id)
+);
+insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
+("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
+("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
+("4", "1", "sup", "0");
+create table t3 (
+id int(11) not null default '0',
+preceeding_id int(11) not null default '0',
+primary key (id,preceeding_id)
+);
+create table t4 (
+user_id varchar(50) not null,
+article_id varchar(20) not null,
+primary key (user_id,article_id)
+);
+insert into t4 values("nicke", "imp");
+prepare stmt from
+'select distinct t1.partner_id
+from t1 left join t3 on t1.id = t3.id
+ left join t1 pp on pp.id = t3.preceeding_id
+where
+ exists (
+ select *
+ from t2 as pl_inner
+ where pl_inner.id = t1.id
+ and pl_inner.sequence <= (
+ select min(sequence) from t2 pl_seqnr
+ where pl_seqnr.id = t1.id
+ )
+ and exists (
+ select * from t4
+ where t4.article_id = pl_inner.article_id
+ and t4.user_id = ?
+ )
+ )
+ and t1.id = ?
+group by t1.id
+having count(pp.id) = 0';
+set @user_id = 'nicke';
+set @id = '2';
+execute stmt using @user_id, @id;
+partner_id
+execute stmt using @user_id, @id;
+partner_id
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index bb1052c7337..24276acf933 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -581,3 +581,71 @@ execute stmt;
execute stmt;
deallocate prepare stmt;
drop table t1;
+
+#
+# Bug#11458 "Prepared statement with subselects return random data":
+# drop PARAM_TABLE_BIT from the list of tables used by a subquery
+#
+create table t1 (
+ id int(11) unsigned not null primary key auto_increment,
+ partner_id varchar(35) not null,
+ t1_status_id int(10) unsigned
+);
+
+insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
+ ("3", "partner3", "10"), ("4", "partner4", "10");
+
+create table t2 (
+ id int(11) unsigned not null default '0',
+ t1_line_id int(11) unsigned not null default '0',
+ article_id varchar(20),
+ sequence int(11) not null default '0',
+ primary key (id,t1_line_id)
+);
+
+insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
+ ("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
+ ("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
+ ("4", "1", "sup", "0");
+
+create table t3 (
+ id int(11) not null default '0',
+ preceeding_id int(11) not null default '0',
+ primary key (id,preceeding_id)
+);
+
+create table t4 (
+ user_id varchar(50) not null,
+ article_id varchar(20) not null,
+ primary key (user_id,article_id)
+);
+
+insert into t4 values("nicke", "imp");
+
+prepare stmt from
+'select distinct t1.partner_id
+from t1 left join t3 on t1.id = t3.id
+ left join t1 pp on pp.id = t3.preceeding_id
+where
+ exists (
+ select *
+ from t2 as pl_inner
+ where pl_inner.id = t1.id
+ and pl_inner.sequence <= (
+ select min(sequence) from t2 pl_seqnr
+ where pl_seqnr.id = t1.id
+ )
+ and exists (
+ select * from t4
+ where t4.article_id = pl_inner.article_id
+ and t4.user_id = ?
+ )
+ )
+ and t1.id = ?
+group by t1.id
+having count(pp.id) = 0';
+set @user_id = 'nicke';
+set @id = '2';
+execute stmt using @user_id, @id;
+execute stmt using @user_id, @id;
+
diff --git a/sql/item.cc b/sql/item.cc
index 3bdaf856f2a..19b88c115b9 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -889,6 +889,7 @@ void Item_param::set_null()
max_length= 0;
decimals= 0;
state= NULL_VALUE;
+ item_type= Item::NULL_ITEM;
DBUG_VOID_RETURN;
}
@@ -1339,34 +1340,6 @@ 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;
-}
-
bool Item_param::basic_const_item() const
{
diff --git a/sql/item.h b/sql/item.h
index 8de2adeb730..a8b0bfe9512 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -564,7 +564,6 @@ 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 20ba838e61c..dec32398a80 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -119,7 +119,6 @@ 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 */
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index cc58e34d582..953bcf5585c 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -251,6 +251,8 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
#define UNCACHEABLE_SIDEEFFECT 4
// forcing to save JOIN for explain
#define UNCACHEABLE_EXPLAIN 8
+/* Don't evaluate subqueries in prepare even if they're not correlated */
+#define UNCACHEABLE_PREPARE 16
#ifdef EXTRA_DEBUG
/*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 927982e444f..07b5c9d8edf 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -257,6 +257,7 @@ public:
UNCACHEABLE_RAND
UNCACHEABLE_SIDEEFFECT
UNCACHEABLE_EXPLAIN
+ UNCACHEABLE_PREPARE
*/
uint8 uncacheable;
enum sub_select_type linkage;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index d7fd3239df5..dc55a842263 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4123,6 +4123,14 @@ mysql_new_select(LEX *lex, bool move_down)
select_lex->select_number= ++lex->thd->select_number;
select_lex->init_query();
select_lex->init_select();
+ /*
+ Don't evaluate this subquery during statement prepare even if
+ it's a constant one. The flag is switched off in the end of
+ mysql_stmt_prepare.
+ */
+ if (lex->thd->current_arena->is_stmt_prepare())
+ select_lex->uncacheable|= UNCACHEABLE_PREPARE;
+
if (move_down)
{
lex->subqueries= TRUE;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 9e2612c5661..f7e2bd0467e 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1657,13 +1657,18 @@ int mysql_stmt_prepare(THD *thd, char *packet, uint packet_length,
{
stmt->setup_set_params();
SELECT_LEX *sl= stmt->lex->all_selects_list;
- /*
- Save WHERE clause pointers, because they may be changed during query
- optimisation.
- */
for (; sl; sl= sl->next_select_in_list())
{
+ /*
+ Save WHERE clause pointers, because they may be changed
+ during query optimisation.
+ */
sl->prep_where= sl->where;
+ /*
+ Switch off a temporary flag that prevents evaluation of
+ subqueries in statement prepare.
+ */
+ sl->uncacheable&= ~UNCACHEABLE_PREPARE;
}
stmt->state= Item_arena::PREPARED;
}
View Lorry Controller Status