From a7ed4644a6f3e38dbad3658fc35890ce31cc0749 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Mon, 3 Jul 2017 13:35:32 +0200 Subject: MDEV-10146: Wrong result (or questionable result and behavior) with aggregate function in uncorrelated SELECT subquery When outer reference resolved in a VIEW it still should mark aggregate function resolving border. --- mysql-test/r/subselect_no_semijoin.result | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/r/subselect_no_semijoin.result') diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index b6261f05098..936aae07227 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7111,5 +7111,27 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); f1 f2 foo bar DROP TABLE t1; +# +# MDEV-10146: Wrong result (or questionable result and behavior) +# with aggregate function in uncorrelated SELECT subquery +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +( SELECT MAX(f1) FROM t2 ) +2 +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +( SELECT MAX(f1) FROM t2 ) +2 +INSERT INTO t2 VALUES (4); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +ERROR 21000: Subquery returns more than 1 row +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; +ERROR 21000: Subquery returns more than 1 row +drop view v1; +drop table t1,t2; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; -- cgit v1.2.1 From 6b99859fff6b8c0a52ea45965834c9c3fdfc4cb3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 6 Jul 2017 23:26:18 +0200 Subject: after-merge fix for a7ed4644a6f (10.0+ changes, as specified in the MDEV) and remove unused variable (compiler warning) --- mysql-test/r/subselect_no_semijoin.result | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) (limited to 'mysql-test/r/subselect_no_semijoin.result') diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 774c18020d8..3896adbfdd2 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7112,9 +7112,10 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -3 +ERROR 42000: Can't group on 'sq' SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; f2 3 @@ -7122,9 +7123,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); count(*) 1 delete from t1; +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +ERROR 42000: Can't group on 'sq' SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; -sq -NULL +ERROR 42000: Can't group on 'sq' drop view v2; drop table t1,t2; # -- cgit v1.2.1