Bug #12747 Multiple JOINs fail with 5.0.12 (column not found)
Submitted: 23 Aug 2005 6:03 Modified: 25 Aug 2005 11:58
Reporter: Martin Karch Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.12 (all) OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[23 Aug 2005 6:03] Martin Karch
Description:
With 5.0.12 some of my VIEWs are not working any more; with 5.0.11 they worked. 

I get the message that a column of the query is not found, but it exists. If I execute the query manually, I get the error, too.

How to repeat:
CREATE TABLE `tbl_abteilungen` (
  `Abt_ID` tinyint(3) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`Abt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_personaldaten` (
  `Pers_ID` int(10) unsigned NOT NULL auto_increment,
  `PersNr` int(10) unsigned NOT NULL default '0',
  `Name` varchar(45) NOT NULL default '',
  `Vorname` varchar(45) NOT NULL default '',
  `Strasse` varchar(45) NOT NULL default '',
  `LKZ` char(3) NOT NULL default 'D' COMMENT 'Laenderkennzeichen',
  `PLZ` varchar(45) NOT NULL default '',
  `Ort` varchar(45) NOT NULL default '',
  `Tel` varchar(45) default NULL,
  `Fax` varchar(45) default NULL,
  `Geburtstag` date default NULL,
  `Abt_ID` tinyint(3) unsigned default NULL,
  `Anrede_ID` tinyint(3) unsigned default NULL,
  `Flag_Virtual` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Virtueller User (z.B. Spezialmonteure wie Ablehnung)',
  `Flag_Del` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Pers_ID`),
  UNIQUE KEY `PersNr` (`PersNr`),
  KEY `FK_tbl_personaldaten_1` (`Abt_ID`),
  KEY `FK_tbl_personaldaten_2` (`Anrede_ID`),
  CONSTRAINT `tbl_personaldaten_ibfk_1` FOREIGN KEY (`Abt_ID`) REFERENCES `tbl_abteilungen` (`Abt_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_personaldaten_ibfk_2` FOREIGN KEY (`Anrede_ID`) REFERENCES `tbl_anrede` (`Anrede_ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 537600 kB';

CREATE TABLE `tbl_systemparameter` (
  `SysPar_ID` int(10) unsigned NOT NULL auto_increment,
  `Pers_ID` int(10) unsigned NOT NULL default '0',
  `Username` varchar(45) NOT NULL default '',
  `Systemlaufwerk` char(1) NOT NULL default '',
  `Faxordner` varchar(45) NOT NULL default '',
  `Berechtigungen` varchar(15) default NULL,
  `Firmen_ID` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`SysPar_ID`),
  UNIQUE KEY `Username` (`Username`),
  UNIQUE KEY `FK_tbl_systemparameter_1` (`Pers_ID`),
  KEY `FK_tbl_systemparameter_2` (`Firmen_ID`),
  CONSTRAINT `tbl_systemparameter_ibfk_1` FOREIGN KEY (`Pers_ID`) REFERENCES `tbl_personaldaten` (`Pers_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_systemparameter_ibfk_2` FOREIGN KEY (`Firmen_ID`) REFERENCES `tbl_firmen` (`Firmen_ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 537600 kB';

The VIEW Query:

select `auftragsverwaltung`.`tbl_personaldaten`.`Pers_ID` AS `Pers_ID`,`auftragsverwaltung`.`tbl_personaldaten`.`Name` AS `Name`,`auftragsverwaltung`.`tbl_personaldaten`.`Vorname` AS `Vorname`,`auftragsverwaltung`.`tbl_systemparameter`.`Username` AS `Username`,`auftragsverwaltung`.`tbl_systemparameter`.`Berechtigungen` AS `Berechtigungen`,`auftragsverwaltung`.`tbl_systemparameter`.`Firmen_ID` AS `Firmen_ID`,`auftragsverwaltung`.`tbl_abteilungen`.`Abt_ID` AS `Abt_ID`,`auftragsverwaltung`.`tbl_personaldaten`.`Flag_Del` AS `Flag_Del` from (`auftragsverwaltung`.`tbl_personaldaten` join (`auftragsverwaltung`.`tbl_systemparameter` left join `auftragsverwaltung`.`tbl_abteilungen` on((`auftragsverwaltung`.`tbl_abteilungen`.`Abt_ID` = `auftragsverwaltung`.`tbl_personaldaten`.`Abt_ID`)))) where (`auftragsverwaltung`.`tbl_personaldaten`.`Pers_ID` = `auftragsverwaltung`.`tbl_systemparameter`.`Pers_ID`)

Suggested fix:
?
[25 Aug 2005 11:58] Evgeny Potemkin
See, FROM clause contain this JOIN:
(`tbl_systemparameter` LEFT JOIN `tbl_abteilungen` ON `tbl_abteilungen`.`Abt_ID` =`tbl_personaldaten`.`Abt_ID`). Fields in ON expression have to be from the tables being joined.
Obviuosly `tbl_personaldaten`.`Abt_ID` doesn't belong not to tbl_systemparameter nor to tbl_abteilungen.
[26 Aug 2005 6:05] Martin Karch
Oh thanx, I see. The brackets were set false. But why did it work with previous versions? Does the new version handle its querys stricter than the older ones?

By the way: why does the server save its view querys in one long line? it's very complex to analyze the view if something goes wrong.