Merge bk@192.168.21.1:mysql-5.1

into  mysql.com:/home/hf/work/mrg/mysql-5.1-opt


sql/item_cmpfunc.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged

10 files changed, 197 insertions, 13 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 7a8f59c65f4..992d1cd418b 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -477,6 +477,11 @@ a
 Warnings:
 Warning	1292	Incorrect date value: '19772-07-29' for column 'a' at row 1
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (id int not null);
+INSERT INTO t1 VALUES (1),(2);
+SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
+id
+DROP TABLE t1;
 End of 5.0 tests
 create table t1(f1 char(1));
 insert into t1 values ('a'),('b'),('1');
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1372ac3687c..8bc9350bd6c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3712,12 +3712,6 @@ bb	2
 cc	3
 dd	1
 DROP TABLE t1,t2,t3;
-CREATE TABLE t1 (s1 char(1));
-INSERT INTO t1 VALUES ('a');
-SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
-s1
-a
-DROP TABLE t1;
 CREATE TABLE t1(f1 int);
 CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
 INSERT INTO t1 VALUES (1),(1),(2),(2);
@@ -3886,3 +3880,34 @@ this is a test.	3
 this is a test.	1
 this is a test.	2
 DROP table t1;
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+SELECT COUNT(*), a,
+(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
+FROM t1 GROUP BY a;
+COUNT(*)	a	(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
+2	2	2
+3	3	3
+1	4	1
+SELECT COUNT(*), a,
+(SELECT MIN(m) FROM t2 WHERE m = count(*))
+FROM t1 GROUP BY a;
+COUNT(*)	a	(SELECT MIN(m) FROM t2 WHERE m = count(*))
+2	2	2
+3	3	3
+1	4	1
+SELECT COUNT(*), a       
+FROM t1 GROUP BY a
+HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
+COUNT(*)	a
+2	2
+3	3
+DROP TABLE t1,t2;
+CREATE TABLE t1 (s1 char(1));
+INSERT INTO t1 VALUES ('a');
+SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
+s1
+a
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 03c35d51045..2727a18c8d8 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;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index a8d5388097d..b54bd155c7d 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -168,6 +168,9 @@ dt
 0000-00-00 00:00:00
 0000-00-00 00:00:00
 drop table t1;
+select cast('2006-12-05 22:10:10' as datetime) + 0;
+cast('2006-12-05 22:10:10' as datetime) + 0
+20061205221010.000000
 CREATE TABLE t1(a DATETIME NOT NULL);
 INSERT INTO t1 VALUES ('20060606155555');
 SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555");
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 2c33ffc08d7..447a8e032cb 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1437,4 +1437,12 @@ a
 SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;
 ERROR 42S22: Unknown column 'c' in 'order clause'
 DROP TABLE t1;
+(select 1 into @var) union (select 1);
+ERROR HY000: Incorrect usage of UNION and INTO
+(select 1) union (select 1 into @var);
+select @var;
+@var
+1
+(select 2) union (select 1 into @var);
+ERROR 42000: Result consisted of more than one row
 End of 5.0 tests
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 31352e4c639..e9583f982ff 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -361,6 +361,17 @@ SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
 
 DROP TABLE t1,t2,t3,t4;
 
+#
+# BUG#27362: IN with a decimal expression that may return NULL
+#
+
+CREATE TABLE t1 (id int not null);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests
 
 
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9c2248cbcc2..0f42b3e06a5 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2602,13 +2602,6 @@ SELECT * FROM t1
 
 DROP TABLE t1,t2,t3;
 
-#
-# Bug#20835 (literal string with =any values)
-#
-CREATE TABLE t1 (s1 char(1));
-INSERT INTO t1 VALUES ('a');
-SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
-DROP TABLE t1;
 #                                                                             
 # Bug#23800: Outer fields in correlated subqueries is used in a temporary     
 #            table created for sorting.                                       
@@ -2748,3 +2741,33 @@ SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
 
 DROP table t1;
 
+#
+# Bug #27257: COUNT(*) aggregated in outer query
+#  
+
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+
+SELECT COUNT(*), a,
+       (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
+  FROM t1 GROUP BY a;
+
+SELECT COUNT(*), a,
+       (SELECT MIN(m) FROM t2 WHERE m = count(*))
+  FROM t1 GROUP BY a;
+
+SELECT COUNT(*), a       
+  FROM t1 GROUP BY a
+    HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
+
+DROP TABLE t1,t2;
+
+#
+# Bug#20835 (literal string with =any values)
+#
+CREATE TABLE t1 (s1 char(1));
+INSERT INTO t1 VALUES ('a');
+SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index ed8480ba464..e3703c0da16 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -489,3 +489,44 @@ select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
 
 drop table t1,t2;
 
+#
+# Bug #24484: Aggregate function used in column list subquery gives erroneous 
+# error
+#
+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;
+
+# Gives error, but should work since it is (a, b) is the PK so only one 
+# given match possible
+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;
+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));
+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;
+
+SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
+
+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;
+
+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;
+
+
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 3aa162b4700..9246080630e 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -113,6 +113,12 @@ insert into t1 values ("00-00-00"), ("00-00-00 00:00:00");
 select * from t1;
 drop table t1;
 
+#
+# Bug #16546 DATETIME+0 not always coerced the same way 
+#
+select cast('2006-12-05 22:10:10' as datetime) + 0;
+
+
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index cc93fbd715a..a57ab469ac2 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -913,4 +913,13 @@ SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;
 
 DROP TABLE t1;
 
+#
+# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION.
+#
+--error 1221
+(select 1 into @var) union (select 1);
+(select 1) union (select 1 into @var);
+select @var;
+--error 1172
+(select 2) union (select 1 into @var);
 --echo End of 5.0 tests