diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 37 |
1 files changed, 37 insertions, 0 deletions
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 # |