| Bug #13890 | RIGHT JOIN incorrect parsing | ||
|---|---|---|---|
| Submitted: | 10 Oct 2005 9:04 | Modified: | 11 Oct 2005 20:00 |
| Reporter: | Eduard Dudar | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.13 RC | OS: | Windows (Windows) |
| Assigned to: | CPU Architecture: | Any | |
[11 Oct 2005 10:52]
Valeriy Kravchuk
Thank you for a bug report. The problem is not with view, but with a select itself. I'll mark your report as a duplicate of http://bugs.mysql.com/bug.php?id=13832. I've added a simple test case similar to yours as a comment to it.
[11 Oct 2005 20:00]
Kolbe Kegel
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
I tested this in an unreleased version of MySQL 5.0 and the query exectues without error:
CREATE VIEW QLEVEL_BY_PROGRAM (
-> EDUCLEVEL_NAME,
-> EDUCPROG_NAME,
-> ID_LTOP)
-> AS
-> SELECT education_level.name, education_program.name, level_by_program.id
-> FROM education_program RIGHT JOIN (education_level RIGHT JOIN level_by_program
-> ON education_level.id = level_by_program.ref_level) ON education_program.id =
-> level_by_program.ref_program;
Query OK, 0 rows affected (0.00 sec)
show create table education_level\G
*************************** 1. row ***************************
Table: education_level
Create Table: CREATE TABLE `education_level` (
`id` int(11) default NULL,
`name` char(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table education_program\G
*************************** 1. row ***************************
Table: education_program
Create Table: CREATE TABLE `education_program` (
`id` int(11) default NULL,
`name` char(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table level_by_program\G
*************************** 1. row ***************************
Table: level_by_program
Create Table: CREATE TABLE `level_by_program` (
`id` int(11) default NULL,
`ref_level` int(11) default NULL,
`ref_program` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Description: When I try to create underlying view -------------------------------------------------------------------------- CREATE VIEW QLEVEL_BY_PROGRAM ( EDUCLEVEL_NAME, EDUCPROG_NAME, ID_LTOP) AS SELECT education_level.name, education_program.name, level_by_program.id FROM education_program RIGHT JOIN (education_level RIGHT JOIN level_by_program ON education_level.id = level_by_program.ref_level) ON education_program.id = level_by_program.ref_program; -------------------------------------------------------------------------- MySQL shows me error such as ...can't find column "level_by_program.ref_program" in "on clause"... If I recompose this query with LEFT JOINS it would be correctly executed. How to repeat: Also try to create view on similar query... It is not only one query that fails... All queries with RIGHT JOINS can not be performed correctly...