bk commit into 4.1 tree (konstantin:1.2341) BUG#8807

[konstantin]

Below is the list of changes that have just been committed into a local
4.1 repository of kostja. When kostja does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2341 05/07/13 17:26:18 konstantin@stripped +9 -0
  - 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

  sql/sql_prepare.cc
    1.148 05/07/13 17:26:09 konstantin@stripped +1 -0
    Switch off the uncacheable flag from all subqueries after statement
    prepare is done (backport from 5.0)

  sql/sql_parse.cc
    1.451 05/07/13 17:26:09 konstantin@stripped +3 -0
    If in statement prepare, mark all subqueries as uncacheable (backport
    from 5.0)

  sql/sql_lex.h
    1.185 05/07/13 17:26:09 konstantin@stripped +1 -0
    Comment fixed.

  sql/mysql_priv.h
    1.354 05/07/13 17:26:09 konstantin@stripped +2 -0
    Add UNCACHEABLE_PREPARE to mark all subqueries as uncacheable if
    in statement prepare (backport from 5.0).

  sql/item_subselect.h
    1.62 05/07/13 17:26:09 konstantin@stripped +0 -1
    Remove no more existing friend.

  sql/item.h
    1.186 05/07/13 17:26:08 konstantin@stripped +0 -1
    Remove Item_param::fix_fields

  sql/item.cc
    1.212 05/07/13 17:26:08 konstantin@stripped +1 -28
    - 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).

  mysql-test/t/ps.test
    1.35 05/07/13 17:26:08 konstantin@stripped +68 -0
    - add a test case for Bug#11458 "Prepared statement with subselects return 
    random data"

  mysql-test/r/ps.result
    1.36 05/07/13 17:26:08 konstantin@stripped +57 -0
    Bug#11458: test results fixed

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	konstantin
# Host:	dragonfly.local
# Root:	/opt/local/work/mysql-4.1-11458

--- 1.211/sql/item.cc	2005-07-12 20:30:42 +04:00
+++ 1.212/sql/item.cc	2005-07-13 17:26:08 +04:00
@@ -889,6 +889,7 @@
   max_length= 0;
   decimals= 0;
   state= NULL_VALUE;
+  item_type= Item::NULL_ITEM;
   DBUG_VOID_RETURN;
 }
 
@@ -1337,34 +1338,6 @@
                       str_value.charset());
   }
   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;
 }
 
 

--- 1.185/sql/item.h	2005-06-27 17:46:35 +04:00
+++ 1.186/sql/item.h	2005-07-13 17:26:08 +04:00
@@ -564,7 +564,6 @@
   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);

--- 1.353/sql/mysql_priv.h	2005-06-27 17:46:35 +04:00
+++ 1.354/sql/mysql_priv.h	2005-07-13 17:26:09 +04:00
@@ -251,6 +251,8 @@
 #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
 /*

--- 1.184/sql/sql_lex.h	2005-03-23 09:36:42 +03:00
+++ 1.185/sql/sql_lex.h	2005-07-13 17:26:09 +04:00
@@ -257,6 +257,7 @@
       UNCACHEABLE_RAND
       UNCACHEABLE_SIDEEFFECT
       UNCACHEABLE_EXPLAIN
+      UNCACHEABLE_PREPARE
   */
   uint8 uncacheable;
   enum sub_select_type linkage;

--- 1.450/sql/sql_parse.cc	2005-06-28 16:06:12 +04:00
+++ 1.451/sql/sql_parse.cc	2005-07-13 17:26:09 +04:00
@@ -4123,6 +4123,9 @@
   select_lex->select_number= ++lex->thd->select_number;
   select_lex->init_query();
   select_lex->init_select();
+  if (lex->thd->current_arena->is_stmt_prepare())
+    select_lex->uncacheable|= UNCACHEABLE_PREPARE;
+
   if (move_down)
   {
     lex->subqueries= TRUE;

--- 1.61/sql/item_subselect.h	2005-05-04 17:05:53 +04:00
+++ 1.62/sql/item_subselect.h	2005-07-13 17:26:09 +04:00
@@ -119,7 +119,6 @@
   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 */

--- 1.35/mysql-test/r/ps.result	2005-06-20 16:06:57 +04:00
+++ 1.36/mysql-test/r/ps.result	2005-07-13 17:26:08 +04:00
@@ -563,3 +563,60 @@
 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

--- 1.34/mysql-test/t/ps.test	2005-06-20 16:06:57 +04:00
+++ 1.35/mysql-test/t/ps.test	2005-07-13 17:26:08 +04:00
@@ -581,3 +581,71 @@
 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;
+

--- 1.147/sql/sql_prepare.cc	2005-06-27 17:46:36 +04:00
+++ 1.148/sql/sql_prepare.cc	2005-07-13 17:26:09 +04:00
@@ -1664,6 +1664,7 @@
     for (; sl; sl= sl->next_select_in_list())
     {
       sl->prep_where= sl->where;
+      sl->uncacheable&= ~UNCACHEABLE_PREPARE;
     }
     stmt->state= Item_arena::PREPARED;
   }