diff options
| -rw-r--r-- | mysql-test/r/group_by.result | 36 | ||||
| -rw-r--r-- | mysql-test/t/group_by.test | 48 | ||||
| -rw-r--r-- | sql/item.cc | 3 |
3 files changed, 86 insertions, 1 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 1472fdfdd7a..268f290ddca 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1478,3 +1478,39 @@ NULL 1 2 DROP TABLE t1; +CREATE TABLE t1 ( a INT, b INT ); +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; +c (SELECT a FROM t1 WHERE b = c) +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; +c (SELECT a FROM t1 WHERE b = c) +SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; +ERROR 42S22: Reference 'c' not supported (reference to group function) +SET @old_sql_mode = @@sql_mode; +SET @@sql_mode='ONLY_FULL_GROUP_BY'; +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; +c (SELECT a FROM t1 WHERE b = c) +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; +ERROR 42000: non-grouping field 'b' is used in HAVING clause +SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; +ERROR 42S22: Reference 'c' not supported (reference to group function) +INSERT INTO t1 VALUES (1, 1); +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; +c (SELECT a FROM t1 WHERE b = c) +1 1 +INSERT INTO t1 VALUES (2, 1); +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; +SET @@sql_mode = @old_sql_mode; diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 94c2b463aaa..9774056f5ef 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -946,3 +946,51 @@ EXPLAIN SELECT b from t2 GROUP BY b; SELECT b from t2 GROUP BY b; DROP TABLE t1; + +# +# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING +# +CREATE TABLE t1 ( a INT, b INT ); + +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; + +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; + +--error ER_ILLEGAL_REFERENCE +SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; + +SET @old_sql_mode = @@sql_mode; +SET @@sql_mode='ONLY_FULL_GROUP_BY'; + +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; + +--error ER_NON_GROUPING_FIELD_USED +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; + +--error ER_ILLEGAL_REFERENCE +SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c) +FROM t1 +HAVING b = 10; + +INSERT INTO t1 VALUES (1, 1); +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; + +INSERT INTO t1 VALUES (2, 1); +--error ER_SUBQUERY_NO_1_ROW +SELECT b c, (SELECT a FROM t1 WHERE b = c) +FROM t1; + +DROP TABLE t1; +SET @@sql_mode = @old_sql_mode; + + + diff --git a/sql/item.cc b/sql/item.cc index 922c3b7a976..34f6eb9dc61 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3408,7 +3408,7 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list) resolve_ref_in_select_and_group() thd current thread ref column reference being resolved - select the sub-select that ref is resolved against + select the select that ref is resolved against DESCRIPTION Resolve a column reference (usually inside a HAVING clause) against the @@ -3479,6 +3479,7 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select) } if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && + select->having_fix_field && select_ref != not_found_item && !group_by_ref) { /* |