MySQL Bugs: #44839: correlated subquery fails with error 1054.

Bug #44839	correlated subquery fails with error 1054.
Submitted:	13 May 2009 5:06	Modified:	13 May 2009 6:39
Reporter:	Shine Mathew	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	correlated subqery, error 1054, Having clause with correlated column name

Description:
MySQL failed to execute a query having correlated subquery with having clause. It reported error 1054 which is unexpected in this case. 

The query which I used is given below.

SELECT T5.i2
FROM   D3 T3,
       D1 T4,
       D1 T5
WHERE  T5.i2 IN
                 ( SELECT  T6.i3
                 FROM     D2 T6,
                          D1 T7,
                          D1 T8
                          RIGHT OUTER JOIN D1 D1
                          ON       D1.I1 = 'A'
                 WHERE    T8.i2 BETWEEN ( T4.i3 ) AND
                          (
                                   T6.i1
                          )
                 GROUP BY T8.i3,
                          T6.i3
                 HAVING   NOT
                          (
                                   'jazz' BETWEEN (T6.i3) AND (T8.i3)
                          )
                 );

How to repeat:
--Create table statements to reproduce this error is given below

CREATE TABLE V2d1 ( 
                    i1 VARCHAR (5) NOT NULL 
                  , Q1V0 CHAR (195) 
                  , i3 VARCHAR (8) NOT NULL 
                  , Q1V1 CHAR (8)
                  , i2 CHAR (22) 
                  , Q1V4 CHAR (28) 
                  , Q1V2N VARCHAR (35) 
                  , Q1V3 NUMERIC (18) NOT NULL 
                  ) ;

CREATE TABLE V2d2 ( 
                    i3 CHAR (7) 
                  , S1V2 FLOAT (22) NOT NULL 
                  , S1V0 NUMERIC (9, 7) 
                  , i1 CHAR (18) 
                  , S1V1 NUMERIC (7,0) 
                  , i2 VARCHAR (12) NOT NULL 
                  ) ; 

CREATE TABLE d3 ( 
                    i1 VARCHAR (23) 
                  , i2 VARCHAR (18) NOT NULL 
                  , i3 CHAR (12) 
                  ) ; 

CREATE VIEW V1d1(i1, Q1V0, i3, Q1V1, i2, Q1V4, Q1V2, Q1V3) AS 
        SELECT  V2d1.i1,  Q1V0,  i3,  V2d1.Q1V1,  i2,  Q1V4,  V2d1.Q1V2N,  V2d1.Q1V3
        FROM V2d1;

CREATE VIEW d1  AS 
        SELECT  V1d1.i1,  V1d1.Q1V2,  i3,  Q1V3,  Q1V0,  i2,  V1d1.Q1V1,  V1d1.Q1V4
        FROM V1d1;

CREATE VIEW V1d2  AS 
        SELECT  V2d2.S1V0,  V2d2.S1V1,  V2d2.i2,  V2d2.S1V2,  i1,  V2d2.i3 FROM V2d2;
        
CREATE VIEW d2  AS 
        SELECT  V1d2.i1,  V1d2.i2,  i3 FROM V1d2;

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/select.html:

The SQL standard requires that HAVING must reference only columns in the GROUP BY  clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and allows HAVING to refer to columns in the SELECT list and columns in outer subqueries as well. 

There is no  column 't8.i3'  in the SELECT list, this is the reason why you get "1054: Unknown column 't8.i3' in 'having clause'" error.