Merge dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-5.0-opt

into  dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-5.1


mysql-test/r/func_gconcat.result:
  Auto merged
mysql-test/r/func_group.result:
  Auto merged
mysql-test/r/func_str.result:
  Auto merged
mysql-test/r/func_time.result:
  Auto merged
mysql-test/r/group_by.result:
  Auto merged
mysql-test/r/heap_hash.result:
  Auto merged
mysql-test/r/information_schema.result:
  Auto merged
mysql-test/r/innodb_mysql.result:
  Auto merged
mysql-test/r/join_outer.result:
  Auto merged
mysql-test/r/olap.result:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
mysql-test/r/range.result:
  Auto merged
mysql-test/r/row.result:
  Auto merged
mysql-test/r/subselect.result:
  Auto merged
mysql-test/r/type_date.result:
  Auto merged
mysql-test/r/union.result:
  Auto merged
mysql-test/r/view_grant.result:
  Auto merged
mysql-test/t/func_group.test:
  Auto merged
mysql-test/t/func_str.test:
  Auto merged
mysql-test/t/func_time.test:
  Auto merged
mysql-test/t/view.test:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_func.h:
  Auto merged
sql/log.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/opt_range.h:
  Auto merged
sql/opt_sum.cc:
  Auto merged
sql/sql_delete.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_list.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_update.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
mysql-test/t/sp.test:
  SCCS merged
sql/item_cmpfunc.cc:
  SCCS merged
sql/sql_select.cc:
  SCCS merged

1 files changed, 56 insertions, 2 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 141f9ed0f4a..ba16bb9410b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1011,7 +1011,7 @@ INSERT INTO t1 VALUES (1);
 UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
 select * from t1;
 i
-1
+2
 drop table t1;
 CREATE TABLE t1 (a int(1));
 EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
@@ -1203,7 +1203,7 @@ UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
 ERROR 42S22: Unknown column 't.i' in 'field list'
 select * from t1;
 i
-1
+3
 drop table t1;
 CREATE TABLE t1 (
 id int(11) default NULL
@@ -2937,6 +2937,35 @@ retailerID	statusID	changed
 0048	1	2006-01-06 12:37:50
 0059	1	2006-01-06 12:37:50
 drop table t1;
+create table t1(a int, primary key (a));
+insert into t1 values (10);
+create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
+insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
+2	DEPENDENT SUBQUERY	t2	range	b	b	40	NULL	2	Using where
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+a	a	b
+10	3	35989
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
+2	DEPENDENT SUBQUERY	t2	range	b	b	40	NULL	2	Using where
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+a	a	b
+10	1	359
+drop table t1,t2;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
@@ -3368,3 +3397,28 @@ ORDER BY t1.t DESC LIMIT 1);
 i1	i2	t	i1	i2	t
 24	1	2005-05-27 12:40:30	24	1	2006-06-20 12:29:40
 DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+(SELECT i FROM t1) UNION (SELECT i FROM t1);
+i
+SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS 
+(
+(SELECT i FROM t1) UNION 
+(SELECT i FROM t1)
+);
+i
+SELECT * FROM t1 
+WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
+i
+explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
+from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
+from t1' at line 1
+explain select * from t1 where not exists 
+((select t11.i from t1 t11) union (select t12.i from t1 t12));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+4	UNION	t12	system	NULL	NULL	NULL	NULL	0	const row not found
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+DROP TABLE t1;