Merge gkodinov@bk-internal.mysql.com:/home/bk/mysql-5.0-opt

into  magare.gmz:/home/kgeorge/mysql/autopush/B24484-5.0


mysql-test/r/subselect3.result:
  Auto merged
sql/item.h:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged

1 files changed, 53 insertions, 0 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index b975ea8cbdc..6a7a601ccf9 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -645,3 +645,56 @@ a	b	Z
 2	2	0
 3	3	1
 drop table t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
+(1,9,'m');
+CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t2 SELECT * FROM t1;
+SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
+as test FROM t1 GROUP BY a;
+a	MAX(b)	test
+1	9	m
+2	3	h
+3	4	i
+SELECT * FROM t1 GROUP by t1.a
+HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
+HAVING MAX(t2.b+t1.a) < 10));
+a	b	c
+SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b))
+AS test FROM t1 GROUP BY a;
+a	AVG(b)	test
+1	4.0000	NULL
+2	2.0000	k
+3	2.5000	NULL
+SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
+a	b	c
+1	3	c
+2	3	h
+3	3	j
+1	4	d
+3	4	i
+1	9	m
+SELECT a, MAX(b),
+(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 
+LIMIT 1) 
+as cnt, 
+(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
+as t_b,
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
+as t_b,
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
+as t_b
+FROM t1 GROUP BY a;
+a	MAX(b)	cnt	t_b	t_b	t_b
+1	9	1	9	m	m
+2	3	1	3	h	h
+3	4	1	4	i	i
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 
+FROM t1 GROUP BY a;
+a	MAX(b)	test
+1	9	m
+2	3	h
+3	4	i
+DROP TABLE t1, t2;