Bug #34755 error in select statement with joins - unknown column
Submitted: 22 Feb 2008 8:06 Modified: 22 Feb 2008 9:41
Reporter: Hans Baier Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.45 SUSE MySQL RPM and 5.0.51a-commun OS:Linux (opensuse 10.3)
Assigned to: CPU Architecture:Any
Tags: no error in version 4.1.21, parse error in version 5

[22 Feb 2008 8:06] Hans Baier
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.
[22 Feb 2008 9:41] MySQL Verification Team
Thank you for the bug report. Please read: http://dev.mysql.com/doc/refman/5.0/en/join.html

" Join Processing Changes in MySQL 5.0.12

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard. "

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;
Empty set (0.02 sec)

mysql>