Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.1, 10.2, 10.3, 10.4, 10.5
-
None
Description
When the HAVING clause contains a subquery that refers to an outer field and this field is not referred to in any of upper selects the server reports an unexpected error message.
The problem can be reproduced with the following test case:
create table t1 (a int, b int); |
create table t2 (c int, d int); |
insert into t1 values (1,1), (1,3), (2,1); |
insert into t2 values (5,2), (1,3), (5,1); |
select a from t1 group by a having exists (select * from t2 where t1.b=t2.d); |
When executing the above query one gets an unexpected error message:
MariaDB [test]> select a from t1 group by a having exists (select * from t2 where t1.b=t2.d);
|
ERROR 1054 (42S22): Unknown column 'test.t1.b' in 'where clause'
|
Compare with:
MariaDB [test]> select a,b from t1 group by a having exists (select * from t2 where t1.b=t2.d);
|
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | 1 |
|
| 2 | 1 |
|
+------+------+
|
With
set SQL_MODE='only_full_group_by'; |
one still gets an improper error message:
MariaDB [test]> select a from t1 group by a having exists (select * from t2 where t1.b=t2.d);
|
ERROR 1054 (42S22): Unknown column 'test.t1.b' in 'where clause'
|
Compare with
MariaDB [test]> select a,b from t1 group by a having exists (select * from t2 where t1.b=t2.d);
|
ERROR 1055 (42000): 'test.t1.b' isn't in GROUP BY
|