MySQL Bugs: #80231: Column name in having, which is contained in select list '*', produces error

Bug #80231	Column name in having, which is contained in select list '*', produces error
Submitted:	2 Feb 2016 9:46	Modified:	20 Apr 2016 14:58
Reporter:	Su Dylan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=====
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(c2 int);
select c1 from t1Query OK, 0 rows affected (0.00 sec)

mysql> select c1 from t1 where exists(select * from t2 having c2>0);
ERROR 1054 (42S22): Unknown column 'c2' in 'having clause'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
The SELECT should succeed and 'c2' in having clause should be resolved as in the select list of inner subquery, contained in '*'.

How to repeat:

drop table if exists t1,t2;
create table t1(c1 int);
create table t2(c2 int);
select c1 from t1 where exists(select * from t2 having c2>0);

Suggested fix:
The SELECT should succeed and 'c2' in having clause should be resolved as in the select list of inner subquery, contained in '*'.

Thank you for the bug report, the below query isn't a valid group by query i.e Oracle:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> create table t1(c1 int);

Table created.

SQL> create table t2(c2 int);

Table created.

SQL> select c1 from t1 where exists(select * from t2 having c2>0);
select c1 from t1 where exists(select * from t2 having c2>0)
                                                       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Hi Miguel,

I know this is invalid for oracle, but it is accepted as valid for MySQL.
I am using MySQL to try this SQL.

Hi Su,

Yes, we still support HAVING without GROUP BY.

It should work in top query as well as in nested query, so this is a bug. I have repeated it and checked it myself.

Verified.

This behavior goes against latest SQL standards and will probably change some time in the future, that is not yet determined.

The query gets rewritten internally to:

select c1 from t1 where exists (select 1 from t2 having c2>0);

That is, the select list of the EXISTS subquery is replaced with the constant 1. When executing the rewritten subquery as a top-level query, we get the same error:

mysql> select 1 from t2 having c2>0;
ERROR 1054 (42S22): Unknown column 'c2' in 'having clause'

The reason for this failure seems to be that Item_ref::fix_fields() doesn't look for the column in the tables in the from list, only in the select list and the group by clause.

Posted by developer:
 
Noted in 5.7.13 changelog.

The optimizer transformed EXISTS (SELECT * ...) constructs to EXISTS
(SELECT 1 ...) before all columns in the inner subquery had been
resolved, which could result in rejection of valid queries that
included a HAVING clause without GROUP BY in the subquery.