Bug #7423 VIEWs defined as SELECT with corelated SUBQUERY are sometimes buggy

• Submitted: 20 Dec 2004 11:26
• Modified: 15 Jun 2005 8:24
• Reporter: Matthias Leich
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[20 Dec 2004 11:26] Matthias Leich

Description:
Test case derived from NIST tests: script flattab and dml087

DROP TABLE IF EXISTS BASE_VS1;
CREATE TABLE BASE_VS1 (C1 INT, C2 INT);

CREATE VIEW VS3 AS
SELECT A.C2 FROM BASE_VS1 A WHERE A.C2 IN
(SELECT B.C1 FROM BASE_VS1 B WHERE B.C1 < A.C2);

SELECT * FROM VS3;
ERROR HY000: View 'test.VS3' references invalid table(s) or column(s) or function(s)

# It looks like the problem is VIEW related, because a similar SELECT works OK
SELECT X_VS3.C2 FROM
( SELECT A.C2 FROM BASE_VS1 A WHERE A.C2 IN
(SELECT B.C1 FROM BASE_VS1 B WHERE B.C1 < A.C2) ) X_VS3;
C2

DROP VIEW VS3;
# It looks like the correlation is the problem, because the replacement of
# the correlation column by a constant removes the problem
CREATE VIEW VS3 AS
SELECT A.C2 FROM BASE_VS1 A WHERE A.C2 IN
(SELECT B.C1 FROM BASE_VS1 B WHERE B.C1 < 3);
SELECT * FROM VS3;
C2

There are some bug reports (6394, 6894, 6814) with similar problems.
But the current bug seems to have a different reason, because the 
correlation plays such an important role.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1770, 2004-12-19

How to repeat:
Please execute the statements above

[15 Jun 2005 8:24] Oleksandr Byelkin

Thank you for bugreport, but I can't repeat this bug any more:
+ DROP TABLE IF EXISTS BASE_VS1;
+ Warnings:
+ Note  1051    Unknown table 'BASE_VS1'
+ CREATE TABLE BASE_VS1 (C1 INT, C2 INT);
+ CREATE VIEW VS3 AS
+ SELECT A.C2 FROM BASE_VS1 A WHERE A.C2 IN
+ (SELECT B.C1 FROM BASE_VS1 B WHERE B.C1 < A.C2);
+ SELECT * FROM VS3;
+ C2

[1 Nov 2006 16:48] Matthias Leich

I can confirm that all problems within the NIST tests
related to this bug disappeared.
mysql-5.0 ChangeSet@1.2290, 2006-10-24