[Commits] 7b5a04c14fb: MDEV-10146: Wrong result (or questionable result and behavior) with aggregate function in uncorrelated SELECT subquery

Oleksandr Byelkin sanja at mariadb.com
Mon Jun 26 12:57:56 EEST 2017


revision-id: 7b5a04c14fb93fa6ebeaad4b40f028bf547bce14 (mariadb-10.2.6-64-g7b5a04c14fb)
parent(s): 0288fa619f4cf95dd4725f16c11804cbd2b3bbab
committer: Oleksandr Byelkin
timestamp: 2017-06-26 11:57:55 +0200
message:

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/group_by.result                  | 22 ++++++++++++++++++++++
 mysql-test/r/subselect.result                 | 10 ++++++----
 mysql-test/r/subselect_no_exists_to_in.result | 10 ++++++----
 mysql-test/r/subselect_no_mat.result          | 10 ++++++----
 mysql-test/r/subselect_no_opts.result         | 10 ++++++----
 mysql-test/r/subselect_no_scache.result       | 10 ++++++----
 mysql-test/r/subselect_no_semijoin.result     | 10 ++++++----
 mysql-test/t/group_by.test                    | 25 +++++++++++++++++++++++++
 mysql-test/t/subselect.test                   |  6 ++++++
 sql/item.cc                                   |  7 +++++++
 10 files changed, 96 insertions(+), 24 deletions(-)

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index bd5f4bc1efd..f23cc401ad4 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2780,3 +2780,25 @@ select distinct 1 from t1 group by a,b with rollup limit 1;
 1
 1
 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;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 85519828b14..c2eeb7fdcc8 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7150,9 +7150,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
@@ -7160,9 +7161,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;
 #
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index deffc030d6b..e3c875bb848 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7150,9 +7150,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
@@ -7160,9 +7161,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;
 #
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 202faf7f522..457fa71ca37 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7143,9 +7143,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
@@ -7153,9 +7154,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;
 #
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 294107ef69e..0457916ae4b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7141,9 +7141,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
@@ -7151,9 +7152,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;
 #
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index b9c06cae5bd..cc626f6a104 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7156,9 +7156,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
@@ -7166,9 +7167,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;
 #
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index e90d70fcc49..ff1b047ac36 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7141,9 +7141,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
@@ -7151,9 +7152,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;
 #
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index acb33c2c115..56e0841b33e 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1884,3 +1884,28 @@ insert into t1 values(-126,7),(1,1),(0,0),(-1,1),(351,65534);
 select distinct 1 from t1 group by a,b with rollup limit 1;
 drop table t1;
 
+
+--echo #
+--echo # MDEV-10146: Wrong result (or questionable result and behavior)
+--echo # with aggregate function in uncorrelated SELECT subquery
+--echo #
+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 ( SELECT MAX(f1) FROM t2 ) FROM v1;
+
+INSERT INTO t2 VALUES (4);
+
+--error ER_SUBQUERY_NO_1_ROW
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+--error ER_SUBQUERY_NO_1_ROW
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+
+drop view v1;
+drop table t1,t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 282013222de..cd390efb2ec 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6008,11 +6008,17 @@ INSERT INTO t2 VALUES (3);
 
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
 
+--error ER_WRONG_GROUP_FIELD
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+--error ER_WRONG_GROUP_FIELD
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 
 delete from t1;
+--error ER_WRONG_GROUP_FIELD
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+--error ER_WRONG_GROUP_FIELD
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
 
 drop view v2;
diff --git a/sql/item.cc b/sql/item.cc
index 61a85f6d487..6eeabdd0bc7 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5237,6 +5237,13 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
                             ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ?
                              (Item_ident*) (*reference) :
                              0));
+          if (thd->lex->in_sum_func &&
+              thd->lex->in_sum_func->nest_level >= select->nest_level)
+          {
+            Item::Type ref_type= (*reference)->type();
+            set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+                          select->nest_level);
+          }
           /*
             A reference to a view field had been found and we
             substituted it instead of this Item (find_field_in_tables


More information about the commits mailing list