| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[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
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