[MDEV-20375] Unexpected error message when using an outer reference in HAVING Created: 2019-08-18  Updated: 2023-04-27

Status: Stalled
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: 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



 Comments   
Comment by Igor Babaev [ 2019-08-19 ]

A really simple query demonstrates this problem:

MariaDB [test]> select a from t1 group by a having t1.b > 1;
 
ERROR 1054 (42S22): Unknown column 't1.b' in 'having clause'

Generated at Thu Feb 08 08:58:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.