Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3
Description
With `sql_mode='ONLY_FULL_GROUP_BY'`, a correlated reference to a non-aggregated, non-grouped outer column is not rejected when it appears inside a subquery's HAVING clause. For example:
CREATE TABLE t1 (a INT PRIMARY KEY, b INT); |
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); |
|
|
SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; |
|
|
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner group by t1_inner.b having t1_outer.a > 1 LIMIT 1) |
FROM t1 AS t1_outer GROUP BY t1_outer.b; |
The result of the above query is indeterminate since `t1_outer.a` in the subquery's having clause is not aggregated nor in group by in the outer query. However, the query executes successfully in MariaDB:
+------------------------------------------------------------------------------------------------+
|
| (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner group by t1_inner.b having t1_outer.a > 1 LIMIT 1) |
|
+------------------------------------------------------------------------------------------------+
|
| NULL |
|
| 3 |
|
| 3 |
|
+------------------------------------------------------------------------------------------------+
|
The same query fails in MySQL with the following error:
ERROR 1055 (42000) at line 6: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.t1_outer.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
|
Attachments
Issue Links
- relates to
-
-
- Stalled
-