Bug #8600 View, more than one natural join, rotten VIEW definition
Submitted: 18 Feb 2005 12:57 Modified: 15 Jun 2005 8:46
Reporter: Matthias Leich Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[18 Feb 2005 12:57] Matthias Leich
Description:
I have a test case derived from the NIST tests, which was successful until around 15 Feb.
It looks like the physical information of a VIEW consisting of more than one natural join
is now rotten.
The bad effects are
 1. SHOW CREATE VIEW fails
 2. SELECT * FROM VIEW fails
with ERROR 42000: You have an error in your SQL syntax; ......
Please have a look into the attached test case.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 last ChangeSet@1.1854, 2005-02-18

BTW: There exists a not complicated and only a bit uncomfortable workaround for
        this bug: decomposition of the join contained within the VIEW definition in
                     such a way, that all affected columns and conditions to be applied
                     are full qualified 
   

How to repeat:
Please use my attached test file ml24.test , copy it to mysql-test/t
  ./mysql-test-run ml24
[18 Feb 2005 12:58] Matthias Leich
test case

Attachment: ml24.test (application/test, text), 1.98 KiB.

[15 Jun 2005 8:46] Oleksandr Byelkin
Thank you for bugreport!
I can't repeat it any more:
+ CREATE TABLE t1 ( COSTCODE INT, COSTTEXT VARCHAR (50) );
+ INSERT INTO t1 VALUES(1       ,'my_costtext');
+ CREATE TABLE t2 ( CONDCODE INT, CONDTEXT VARCHAR (50) );
+ INSERT INTO t2 VALUES(1       ,'my_condtext');
+ CREATE TABLE t4 ( COSTCODE INT , CONDCODE INT );
+ INSERT INTO t4 VALUES(1        ,1);
+ SELECT CONDTEXT, COSTTEXT FROM t4 NATURAL JOIN t1 NATURAL JOIN t2;
+ CONDTEXT      COSTTEXT
+ my_condtext   my_costtext
+ CREATE VIEW v1 AS SELECT CONDTEXT, COSTTEXT FROM t4 NATURAL JOIN t1 NATURAL
+ JOIN t2;
+ SELECT * FROM v1;
+ CONDTEXT      COSTTEXT
+ my_condtext   my_costtext
+ SHOW CREATE VIEW v1;
+ View  Create View
+ v1    CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t2`.`CONDTEXT` AS `CONDTEXT`,`test`.`t1`.`COSTTEXT` AS `COSTTEXT` from ((`test`.`t4` join `test`.`t1`) join `test`.`t2`) where ((`test`.`t1`.`COSTCODE` = `test`.`t4`.`COSTCODE`))
[1 Nov 2006 18:12] 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