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

[2 Feb 2016 9:46] Su Dylan
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 '*'.
[2 Feb 2016 11:07] Miguel Solorzano
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
[3 Feb 2016 8:23] Su Dylan
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.
[3 Feb 2016 15:37] Sinisa Milivojevic
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.
[4 Feb 2016 9:48] Knut Anders Hatlen
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.
[20 Apr 2016 14:58] Paul Dubois
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.