Merge mysql.com:/d2/hf/mrg/mysql-5.0-opt

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt

1 files changed, 56 insertions, 0 deletions

diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3b7cc2abb73..0648c3ebda0 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2819,6 +2819,62 @@ SELECT tt.a, MAX(
   FROM t1 as tt GROUP BY tt.a;
 
 DROP TABLE t1;
+#
+# Bug #27348: SET FUNCTION used in  a subquery from WHERE condition 
+#  
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
+
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+
+SELECT a FROM t1 t0
+  WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+
+SET @@sql_mode='ansi';
+--error 1111
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+--error 1111
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+
+--error 1111
+SELECT a FROM t1 t0
+  WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+
+SET @@sql_mode=default;
+
+DROP TABLE t1;
+
+#
+# Bug #27363: nested aggregates in outer, subquery / sum(select
+# count(outer))
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
+SELECT COUNT(1) FROM DUAL;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 
+  SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
+FROM t1;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT t1.a as XXA, 
+   SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
+FROM t1;
+
+DROP TABLE t1,t2;
+
+--echo End of 5.0 tests.
 
 #
 # Bug #27348: SET FUNCTION used in  a subquery from WHERE condition