Description:
we use a select statement with joins, in one section of this join the column of a table is unknown.
this select statement works in version 4.1.21 but on version 5.0.51a (and 5.0.45) we got an error
ERROR 1054 (42S22): Unknown column 'b.FK_banner_format_id' in 'on clause'
select statement with error out
--------------------------------
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.51a, for unknown-linux-gnu (x86_64) using readline 5.0
Connection id: 6
Current database: tst
Current user: root@localhost
SSL: Not in use
Current pager: less
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-community MySQL Community Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 days 34 min 51 sec
Threads: 1 Questions: 60 Slow queries: 0 Opens: 24 Flush tables: 1 Open tables: 14 Queries per second avg: 0.000
--------------
mysql> SELECT mb.menue_banner_id
-> ,mb.FK_banner_feld_id AS banner_feld_id
-> ,mb.spalte
-> ,mb.zeile
-> ,mb.sortierung
-> ,b.banner_id
-> ,IFNULL(b.quelltext, '') AS quelltext
-> ,b.endung
-> ,IFNULL(b.link, '') AS link
-> ,b.linkziel
-> ,IFNULL(b.layer_x, 0) AS layer_x
-> ,IFNULL(b.layer_y, 0) AS layer_y
-> ,b.zeit
-> ,b.hintergrund
-> ,b.kombiniert
-> ,bf.banner_format_id
-> ,bf.breite
-> ,bf.hoehe
-> ,bff.groesse_x
-> ,bff.groesse_y
-> FROM menue_banner mb
-> ,banner b
-> LEFT JOIN banner_format bf
-> ON bf.banner_format_id = b.FK_banner_format_id
-> LEFT JOIN banner_feld_format bff
-> ON bff.FK_banner_format_id = b.FK_banner_format_id
-> AND bff.FK_banner_feld_id = mb.FK_banner_feld_id
-> WHERE NOW()
-> BETWEEN b.gueltig_von
-> AND b.gueltig_bis
-> AND b.zustand = 'Angezeigt'
-> AND b.banner_id = mb.FK_banner_id
-> AND (
-> (mb.FK_menue_id = 2
-> AND mb.FK_banner_feld_id IN (1,4))
-> OR
-> (mb.FK_banner_feld_id = -1
-> AND mb.wert = -1
-> )
-> )
-> GROUP BY mb.menue_banner_id
-> ORDER BY mb.FK_banner_feld_id
-> ,mb.sortierung
-> ,mb.zeile
-> ,mb.spalte
-> ;
ERROR 1054 (42S22): Unknown column 'mb.FK_banner_feld_id' in 'on clause'
How to repeat:
# select statement
SELECT mb.menue_banner_id
,mb.FK_banner_feld_id AS banner_feld_id
,mb.spalte
,mb.zeile
,mb.sortierung
,b.banner_id
,IFNULL(b.quelltext, '') AS quelltext
,b.endung
,IFNULL(b.link, '') AS link
,b.linkziel
,IFNULL(b.layer_x, 0) AS layer_x
,IFNULL(b.layer_y, 0) AS layer_y
,b.zeit
,b.hintergrund
,b.kombiniert
,bf.banner_format_id
,bf.breite
,bf.hoehe
,bff.groesse_x
,bff.groesse_y
FROM menue_banner mb
,banner b
LEFT JOIN banner_format bf
ON bf.banner_format_id = b.FK_banner_format_id
LEFT JOIN banner_feld_format bff
ON bff.FK_banner_format_id = b.FK_banner_format_id
AND bff.FK_banner_feld_id = mb.FK_banner_feld_id
WHERE NOW()
BETWEEN b.gueltig_von
AND b.gueltig_bis
AND b.zustand = 'Angezeigt'
AND b.banner_id = mb.FK_banner_id
AND (
(mb.FK_menue_id = 2
AND mb.FK_banner_feld_id IN (1,4))
OR
(mb.FK_banner_feld_id = -1
AND mb.wert = -1
)
)
GROUP BY mb.menue_banner_id
ORDER BY mb.FK_banner_feld_id
,mb.sortierung
,mb.zeile
,mb.spalte;
# # # # # # # # # # #
# ----------------------------------
# create this table in a database xx
# ----------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `banner`
--
DROP TABLE IF EXISTS `banner`;
CREATE TABLE `banner` (
`banner_id` int(10) unsigned NOT NULL auto_increment,
`FK_banner_format_id` int(10) unsigned NOT NULL default '0',
`geaendert` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`datum` datetime NOT NULL default '0000-00-00 00:00:00',
`banner` varchar(32) NOT NULL default '',
`firma` varchar(128) NOT NULL default '',
`quelltext` text,
`endung` varchar(8) default NULL,
`link` text,
`linkziel` enum('Neues Fenster','Gleiches Fenster') NOT NULL default 'Neues Fenster',
`hintergrund` varchar(8) default NULL,
`layer_x` int(10) unsigned default NULL,
`layer_y` int(10) unsigned default NULL,
`zeit` int(10) unsigned default NULL,
`kombiniert` enum('Ja','Nein') NOT NULL default 'Nein',
`gueltig_von` datetime NOT NULL default '0000-00-00 00:00:00',
`gueltig_bis` datetime NOT NULL default '0000-00-00 00:00:00',
`zustand` enum('Angezeigt','Gesperrt') NOT NULL default 'Angezeigt',
`angezeigt` int(10) unsigned NOT NULL default '0',
`klicks` int(10) unsigned NOT NULL default '0',
`FK_banner_default_id` int(10) unsigned default NULL,
PRIMARY KEY (`banner_id`),
KEY `werbebanner_index1` (`zustand`),
KEY `werbebanner_index2` (`gueltig_von`),
KEY `werbebanner_index3` (`gueltig_bis`)
) ENGINE=MyISAM AUTO_INCREMENT=126 DEFAULT CHARSET=latin1;
--
-- Table structure for table `menue_banner`
--
DROP TABLE IF EXISTS `menue_banner`;
CREATE TABLE `menue_banner` (
`menue_banner_id` int(10) unsigned NOT NULL auto_increment,
`FK_banner_id` int(10) unsigned NOT NULL default '0',
`FK_menue_id` int(10) unsigned default NULL,
`FK_banner_feld_id` int(10) unsigned default NULL,
`geaendert` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`datum` datetime NOT NULL default '0000-00-00 00:00:00',
`wert` int(10) unsigned default NULL,
`spalte` int(10) unsigned default NULL,
`zeile` int(10) unsigned default NULL,
`sortierung` int(10) unsigned default NULL,
PRIMARY KEY (`menue_banner_id`),
KEY `menue_banner_index1` (`FK_menue_id`,`FK_banner_feld_id`),
KEY `menue_banner_index2` (`FK_banner_feld_id`,`FK_menue_id`),
KEY `menue_banner_index3` (`wert`,`FK_banner_feld_id`),
KEY `menue_banner_index4` (`FK_banner_feld_id`,`wert`)
) ENGINE=MyISAM AUTO_INCREMENT=687 DEFAULT CHARSET=latin1;
--
-- Table structure for table `banner_format`
--
DROP TABLE IF EXISTS `banner_format`;
CREATE TABLE `banner_format` (
`banner_format_id` int(10) unsigned NOT NULL auto_increment,
`banner_format` char(64) NOT NULL default '',
`geaendert` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`datum` datetime NOT NULL default '0000-00-00 00:00:00',
`breite` int(10) unsigned NOT NULL default '0',
`hoehe` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`banner_format_id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
--
-- Table structure for table `banner_feld_format`
--
DROP TABLE IF EXISTS `banner_feld_format`;
CREATE TABLE `banner_feld_format` (
`FK_banner_format_id` int(10) unsigned NOT NULL default '0',
`FK_banner_feld_id` int(10) unsigned NOT NULL default '0',
`groesse_x` int(10) unsigned NOT NULL default '0',
`groesse_y` int(10) unsigned NOT NULL default '0',
`sortierung` enum('Ja','Nein') NOT NULL default 'Nein',
PRIMARY KEY (`FK_banner_format_id`,`FK_banner_feld_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Suggested fix:
we have no fix. we cant change the version until this error is fixed.