bk commit into 5.1 tree (gkodinov:1.2590)

[kgeorge]

Below is the list of changes that have just been committed into a local
5.1 repository of kgeorge. When kgeorge 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@stripped, 2007-04-20 12:06:52+03:00, gkodinov@stripped +6 -0
  Merge magare.gmz:/home/kgeorge/mysql/work/B27786-5.0-opt
  into  magare.gmz:/home/kgeorge/mysql/work/B27786-addon-5.1-opt
  MERGE: 1.1810.2374.134

  mysql-test/r/subselect3.result@stripped, 2007-04-20 12:01:10+03:00, gkodinov@stripped +0
-0
    Auto merged
    MERGE: 1.1.1.8

  mysql-test/r/view.result@stripped, 2007-04-20 12:01:10+03:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.138.1.61

  mysql-test/t/view.test@stripped, 2007-04-20 12:06:50+03:00, gkodinov@stripped +16 -16
    fixed testcase merge 5.0->5.1
    MERGE: 1.126.1.56

  sql/item_subselect.h@stripped, 2007-04-20 12:01:10+03:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.77.1.12

  sql/sql_lex.h@stripped, 2007-04-20 12:06:50+03:00, gkodinov@stripped +0 -1
    merge fixes : 5.0->5.1
    MERGE: 1.175.1.67

  sql/sql_view.cc@stripped, 2007-04-20 12:01:10+03:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.78.1.30

# 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:	gkodinov
# Host:	magare.gmz
# Root:	/home/kgeorge/mysql/work/B27786-addon-5.1-opt/RESYNC

--- 1.271/sql/sql_lex.h	2007-04-04 12:05:11 +03:00
+++ 1.272/sql/sql_lex.h	2007-04-20 12:06:50 +03:00
@@ -541,6 +541,7 @@ public:
   bool change_result(select_subselect *result, select_subselect *old_result);
   void set_limit(st_select_lex *values);
   void set_thd(THD *thd_arg) { thd= thd_arg; }
+  inline bool is_union (); 
 
   friend void lex_start(THD *thd, const char *buf, uint length);
   friend int subselect_union_engine::exec();
@@ -794,6 +795,12 @@ private:  
   List<index_hint> *index_hints;
 };
 typedef class st_select_lex SELECT_LEX;
+
+inline bool st_select_lex_unit::is_union ()
+{ 
+  return first_select()->next_select() && 
+    first_select()->next_select()->linkage == UNION_TYPE;
+}
 
 #define ALTER_ADD_COLUMN	(1L << 0)
 #define ALTER_DROP_COLUMN	(1L << 1)

--- 1.219/mysql-test/r/view.result	2007-04-14 00:32:18 +03:00
+++ 1.220/mysql-test/r/view.result	2007-04-20 12:01:10 +03:00
@@ -3311,6 +3311,41 @@ lgid	clid
 2	YES
 DROP VIEW v1;
 DROP table t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;
+SELECT * FROM t1 UNION SELECT * FROM v1;
+a
+1
+2
+3
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM v1 UNION SELECT * FROM t1;
+a
+1
+2
+3
+EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+a
+1
+2
+3
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;

--- 1.194/mysql-test/t/view.test	2007-04-14 00:32:18 +03:00
+++ 1.195/mysql-test/t/view.test	2007-04-20 12:06:50 +03:00
@@ -3197,6 +3197,22 @@ SELECT * FROM v1;
 DROP VIEW v1;
 DROP table t1,t2;
 
+#
+# Bug#27786: Inconsistent Operation Performing UNION On View With ORDER BY 
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3);
+CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;
+
+SELECT * FROM t1 UNION SELECT * FROM v1;
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1;
+SELECT * FROM v1 UNION SELECT * FROM t1;
+EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1;
+SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.
 
 #

--- 1.92/sql/item_subselect.h	2007-03-06 17:55:03 +02:00
+++ 1.93/sql/item_subselect.h	2007-04-20 12:01:10 +03:00
@@ -277,7 +277,11 @@ public:
   {
     return pushed_cond_guards ? pushed_cond_guards + i : NULL;
   }
-  void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; }
+  void set_cond_guard_var(int i, bool v) 
+  { 
+    if ( pushed_cond_guards)
+      pushed_cond_guards[i]= v;
+  }
   bool have_guarded_conds() { return test(pushed_cond_guards); }
 
   Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery

--- 1.127/sql/sql_view.cc	2007-04-03 13:30:00 +03:00
+++ 1.128/sql/sql_view.cc	2007-04-20 12:01:10 +03:00
@@ -1270,13 +1270,18 @@ bool mysql_make_view(THD *thd, File_pars
         unit->slave= save_slave; // fix include_down initialisation
       }
 
+      /* 
+        We can safely ignore the VIEW's ORDER BY if we merge into union 
+        branch, as order is not important there.
+      */
+      if (!table->select_lex->master_unit()->is_union())
+       
table->select_lex->order_list.push_back(&lex->select_lex.order_list);
       /*
 	This SELECT_LEX will be linked in global SELECT_LEX list
 	to make it processed by mysql_handle_derived(),
 	but it will not be included to SELECT_LEX tree, because it
 	will not be executed
-      */
-      table->select_lex->order_list.push_back(&lex->select_lex.order_list);
+      */ 
       goto ok;
     }
 

--- 1.9/mysql-test/r/subselect3.result	2007-04-15 11:01:02 +03:00
+++ 1.10/mysql-test/r/subselect3.result	2007-04-20 12:01:10 +03:00
@@ -692,3 +692,22 @@ a	MAX(b)	test
 2	3	h
 3	4	i
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int, PRIMARY KEY(b));
+INSERT INTO t1 VALUES (1), (NULL), (4);
+INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
+EXPLAIN EXTENDED 
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where
((`test`.`t2`.`b` = `test`.`t1`.`a`) and
(not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from
`test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or
isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`))))))
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
+a
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
+a
+1
+4
+DROP TABLE t1,t2;