summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/subselect.result67
-rw-r--r--mysql-test/r/subselect3.result4
-rw-r--r--mysql-test/t/subselect.test37
-rw-r--r--mysql-test/t/subselect3.test3
4 files changed, 105 insertions, 6 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 089fb10aaae..4023d613854 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -48,7 +48,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
@@ -3924,6 +3924,71 @@ c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3 3 4
1 4 2,2
DROP table t1,t2;
+CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
+(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
+(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
+FROM t1 GROUP BY a;
+a MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+SELECT a x, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
+FROM t1 GROUP BY a;
+x MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+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 WHERE t1.d=0 GROUP BY a;
+a AVG(b) test
+1 4.0000 d
+2 2.0000 g
+3 2.5000 NULL
+SELECT tt.a,
+(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
+LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
+FROM t1 as tt;
+a test
+1 n
+1 n
+1 n
+1 n
+1 n
+1 n
+1 n
+2 o
+2 o
+2 o
+2 o
+3 p
+3 p
+3 p
+3 p
+3 p
+SELECT tt.a,
+(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
+LIMIT 1)
+FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
+FROM t1 as tt GROUP BY tt.a;
+a test
+1 n
+2 o
+3 p
+SELECT tt.a, MAX(
+(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
+LIMIT 1)
+FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
+FROM t1 as tt GROUP BY tt.a;
+a test
+1 n
+2 o
+3 p
+DROP TABLE t1;
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;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 4b8ece1c931..18feb7cb6b9 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -432,7 +432,7 @@ alter table t1 add index idx(oref,ie);
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 t2.oref,func 4 Using where; Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
oref a Z
ee NULL NULL
@@ -457,7 +457,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 88c34bdb9b9..3b7cc2abb73 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2784,6 +2784,43 @@ SELECT COUNT(*) c, a,
DROP table t1,t2;
#
+# Bug#27321: Wrong subquery result in a grouping select
+#
+CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
+(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
+(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
+
+SELECT a, MAX(b),
+ (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
+ FROM t1 GROUP BY a;
+SELECT a x, MAX(b),
+ (SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
+ FROM t1 GROUP BY a;
+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 WHERE t1.d=0 GROUP BY a;
+
+SELECT tt.a,
+ (SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
+ LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
+ FROM t1 as tt;
+
+SELECT tt.a,
+ (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
+ LIMIT 1)
+ FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
+ FROM t1 as tt GROUP BY tt.a;
+
+SELECT tt.a, MAX(
+ (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
+ LIMIT 1)
+ FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
+ FROM t1 as tt GROUP BY tt.a;
+
+DROP TABLE t1;
+
+#
# Bug #27348: SET FUNCTION used in a subquery from WHERE condition
#
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index e8eae3e2452..11468cd6759 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -507,9 +507,6 @@ SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
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));
-#FIXME: Enable this test after fixing bug #27321
-#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;
View Lorry Controller Status