MySQL Bugs: #8468: Problem with VIEW

Bug #8468	Problem with VIEW
Submitted:	12 Feb 2005 5:52	Modified:	15 Jul 2005 8:17
Reporter:	Robert Sillett	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha	OS:	Apple OS X
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I realize that this is alpha code.  

I can issue a SELECT statement and get results.  But when I make that SELECT statement into a view, then try to do a SELECT * FROM view, I get a message that one of the underlying tables is missing.  

It's quite odd behavior.  Here is the SELECT statement:

SELECT tblHFCC_TRANSMISSIONS.Frequency, tblHFCC_TRANSMISSIONS.Start, tblHFCC_TRANSMISSIONS.Stop, tblHFCC_BROADCAST.Broadcaster AS Station, tblHFCC_SITE.Site_Name AS Location, tblHFCC_ADMIN.English AS Country, tblHFCC_TRANSMISSIONS.kW, tblHFCC_TRANSMISSIONS.Days, tblHFCC_TRANSMISSIONS.Lang, tblHFCC_TRANSMISSIONS.Azimuth, tblHFCC_SITE.Latitude, tblHFCC_SITE.Longitude, tblHFCC_TRANSMISSIONS.CIRAF_Zones, tblHFCC_TRANSMISSIONS.FMO, tblHFCC_TRANSMISSIONS.First_Date, tblHFCC_TRANSMISSIONS.Termination_Date
FROM tblHFCC_BROADCAST INNER JOIN (tblHFCC_ADMIN INNER JOIN (tblHFCC_SITE INNER JOIN tblHFCC_TRANSMISSIONS ON tblHFCC_SITE.LOC = tblHFCC_TRANSMISSIONS.LOC) ON tblHFCC_ADMIN.ADM = tblHFCC_TRANSMISSIONS.ADM) ON tblHFCC_BROADCAST.BRC = tblHFCC_TRANSMISSIONS.BRC;

The above syntax was directly cut and pasted from Microsoft Access.  What I am doing is testing MySQL 5.x by porting a database from Access to MySQL.  I was quite excited to see the Access-formatted SELECT statement work with no problems.  But as soon as I made it a view, I get the error message "ERROR 1109 (42S02): Unknown table 'tblHFCC_ADMIN' in on clause"

How to repeat:
Go to http://www.hfcc.org and download the B04 database from the Public Area.  HFCC is a quasi-governmental body that coordinates shortwave radio frequencies from various countries. 

Create the tables, import the data, and make the first column the primary key on each table, run the select statment and see it work.  Put the select statement into a view and see it fail with an "ERROR 1109 (42S02): Unknown table 'tblHFCC_ADMIN' in on clause"

I'd be more than happy to email/ftp a dump of my tables and data if you wish.

Again, the SELECT statment works perfectly.  But as a VIEW it fails.

All the tables and data described in the bug report.

Attachment: hfcc_data.sql.gz (application/x-gzip, text), 120.76 KiB.

As this is my first bug report, I did not know that one could add a file.  I have added a dump of all the tables involved.

This bug is the one thing that's stopping me from converting all my existing MSAccess backends to MySQL. Hope it's fixed soon.

i also have this error with 5.0.7 on Win2000 and 5.0.8 on Linux

my SELECT:

select `auftragsverwaltung`.`tbl_reklamationspositionen`.`Rekl_ID` AS `Rekl_ID`,
`auftragsverwaltung`.`tbl_reklamationspositionen`.`Pos` AS `Pos`,
`auftragsverwaltung`.`tbl_reklamationspositionen`.`Stk` AS `Anzahl`,
`auftragsverwaltung`.`tbl_produktgruppen`.`PGNr` AS `PG`,
`auftragsverwaltung`.`tbl_fehlerarten`.`FANr` AS `FA`,
`auftragsverwaltung`.`tbl_fehlerursachen`.`FUNr` AS `FU`,
`auftragsverwaltung`.`tbl_produkte`.`ProduktNr` AS `ProduktNr`,
`auftragsverwaltung`.`tbl_produkte`.`ProduktBez` AS `ProduktBez` 

from (`auftragsverwaltung`.`tbl_reklamationspositionen` 
join ((`auftragsverwaltung`.`tbl_produkte` 
join `auftragsverwaltung`.`tbl_produktgruppen` 
on((`auftragsverwaltung`.`tbl_produkte`.`PG_ID` = `auftragsverwaltung`.`tbl_produktgruppen`.`PG_ID`))) 
left join ((`auftragsverwaltung`.`tbl_fehlerschluessel` 
join `auftragsverwaltung`.`tbl_fehlerarten` 
on((`auftragsverwaltung`.`tbl_fehlerarten`.`FA_ID` = `auftragsverwaltung`.`tbl_fehlerschluessel`.`FA_ID`))) 

join `auftragsverwaltung`.`tbl_fehlerursachen` 
on((`auftragsverwaltung`.`tbl_fehlerursachen`.`FU_ID` = `auftragsverwaltung`.`tbl_fehlerschluessel`.`FU_ID`))) 
on((`auftragsverwaltung`.`tbl_fehlerschluessel`.`FS_ID` = `auftragsverwaltung`.`tbl_reklamationspositionen`.`FS_ID`))) 
on((`auftragsverwaltung`.`tbl_reklamationspositionen`.`Produkt_ID` = `auftragsverwaltung`.`tbl_produkte`.`Produkt_ID`)))

if my DB is needed let me know.

Greetz
  Martin Karch

Thank you for bugreport!
I can't repeat this bug any more. It is fixed in current bk source repository of 5.0 by some other bugfix.