- a fix for Bug#11458 "Prepared statement with subselects return random

data": remove the fix for another bug (8807) that
added OUTER_REF_TABLE_BIT to all subqueries that used a placeholder
to prevent their evaluation at prepare. As this bit hanged in 
Item_subselect::used_tables_cache for ever, a constant subquery with
a placeholder was never evaluated as such, which caused wrong 
choice of the execution plan for the statement.
- to fix Bug#8807 backport a better fix from 5.0
- post-review fixes.


mysql-test/r/ps.result:
  Bug#11458: test results fixed
mysql-test/t/ps.test:
  - add a test case for Bug#11458 "Prepared statement with subselects return 
  random data"
sql/item.cc:
  - remove unnecessary Item_param::fix_fields
  - fix Item_param::set_null to set item_type accordingly (safety:
    Item_param should behave like a basic constant).
sql/item.h:
  Remove Item_param::fix_fields
sql/item_subselect.h:
  Remove no more existing friend.
sql/mysql_priv.h:
  Add UNCACHEABLE_PREPARE to mark all subqueries as uncacheable if
  in statement prepare (backport from 5.0).
sql/sql_lex.h:
  Comment fixed.
sql/sql_parse.cc:
  If in statement prepare, mark all subqueries as uncacheable (backport
  from 5.0)
sql/sql_prepare.cc:
  Switch off the uncacheable flag from all subqueries after statement
  prepare is done (backport from 5.0)

2 files changed, 125 insertions, 0 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;
+