MySQL Bugs: #23711: #1054 - Unknown column in 'on clause'

Bug #23711	#1054 - Unknown column in 'on clause'
Submitted:	27 Oct 2006 10:16	Modified:	27 Oct 2006 12:55
Reporter:	Dan Kokenge	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S1 (Critical)
Version:	5.0.24	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
This SQL command has been working fine for years, With the new release it quit working.

I get the error:
#1054 - Unknown column 'wpo.company_num' in 'on clause' 
This is the SQL:

SELECT *
FROM wpo,wpo_dtl
LEFT JOIN company
ON    company.company_num = wpo.company_num
WHERE wpo.wpo_num = wpo_dtl.wpo_num
------------------------
If I change the tables around in the FROM command it works as in: 
FROM wpo_dtl,wpo

Problem is the original command has more tables and if I change the FROM around I get the same error on another table. In other words there is no work around for this, because I can't split the command. I need to sort on outer fields.
--------------------------------------------------
Here is my original command that I reduced to the above.
SELECT *,
wpo.wpo_num	as wpo_num,
wpo_dtl.from_to	as from_to,
wpo_dtl.storage_id	as storage_id,
wpo_varietal.variety_num	as variety_num,
wpo_varietal.area_id	as area_id,
wpo_varietal.year	as year,
wpo_varietal.pcnt	as pcnt,
wpo_varietal.yield 	as yield,
wpo_varietal.qty	as var_qty,
wpo.company_num	as grower_num,
variety.short	as var_descr,
variety.color	as color,
area.short	as area_name,
company.name	as comp_name

FROM wpo,wpo_dtl

LEFT JOIN wpo_varietal
ON	wpo_varietal.wpo_num	= wpo_dtl.wpo_num
AND	wpo_varietal.from_to	= wpo_dtl.from_to
AND	wpo_varietal.storage_id	= wpo_dtl.storage_id

LEFT JOIN company
ON	company.company_num	= wpo.company_num

LEFT JOIN	variety
ON	variety.variety_num	= wpo_varietal.variety_num

LEFT JOIN	area
ON	area.area_id	= wpo_varietal.area_id

WHERE 	wpo.wpo_op	= 'C'
AND	wpo_dtl.wpo_num		= wpo.wpo_num 
AND	wpo.wpo_datetime	= (SELECT MAX(wpo1.wpo_datetime)
 FROM	wpo	as wpo1,
	wpo_dtl	as wpo_dtl1
 WHERE	wpo_dtl1.storage_id	= wpo_dtl.storage_id
	
 AND	wpo_dtl1.wpo_num	= wpo1.wpo_num
 AND	wpo_dtl1.from_to 	= (SELECT MAX(wpo_dtl2.from_to)
  FROM 	wpo_dtl as wpo_dtl2
  WHERE	wpo_dtl2.wpo_num 	= wpo.wpo_num
  AND	wpo_dtl2.storage_id	= wpo_dtl.storage_id)) 

ORDER BY wpo.wpo_datetime desc ,wpo_dtl.from_to ,wpo_dtl.storage_id  LIMIT 0,20

--------------------------
Thank you for your help.
Have a great day...
Dan Kokenge

How to repeat:
Simply execute the SQL command:

SELECT *
FROM wpo,wpo_dtl
LEFT JOIN company
ON	company.company_num	= wpo.company_num
WHERE wpo.wpo_num = wpo_dtl.wpo_num

Suggested fix:
I can move the tables around in the FROM clause and it works:
as in.
FROM wpo_dtl,wpo instead of FROM wpo,wpo_dtl

The problem is when you have a large SQL command, you get errors on other files.

This bug still exists in version 5.0.26 and needs immediate fixing. Please provide me with a diff file, I'll have to make tests for you.

This is my statement

                        SELECT
                                ps.`id`,
                                co.`advancedoptions_id`
                        FROM
                                productsource AS ps,
                                categorydefaults AS cd,
                                categoryoptions AS co
                        LEFT JOIN
                                productsourceoptions AS po
                        ON
                                ps.`id`=po.`productsource_id`
                        AND
                                ps.`id`='23350'
                        AND
                                po.`advancedoptions_id`=co.`advancedoptions_id`
                        WHERE
                                ps.`sadvancedoptions`='update'
                        AND
                                po.`id` IS NULL
                        AND
                                ps.`cat1_id`=cd.`cat1`
                        AND
                                ps.`cat2_id`=cd.`cat2`
                        AND
                                cd.`id`=co.`categorydefaults_id`

depending on the order of the three tables in the FROM part, I will get either
ERROR 1054 (42S22): Unknown column 'co.advancedoptions_id' in 'on clause'
or
ERROR 1054 (42S22): Unknown column io'ps.id' in 'on clause'

Here is the diff you ask for.
If you need more information - let me know.
=======================================================
-- phpMyAdmin SQL Dump
-- version 2.8.2.4
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Oct 27, 2006 at 05:26 AM
-- Server version: 5.0.24
-- PHP Version: 5.1.6
-- 
-- Database: `maytrex_winelite`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `company`
-- 

CREATE TABLE `company` (
  `company_num` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  `addr1` varchar(22) NOT NULL default '',
  `addr2` varchar(22) NOT NULL default '',
  `city` varchar(22) NOT NULL default '',
  `state` char(3) NOT NULL default '',
  `zip` varchar(10) NOT NULL default '',
  `country` varchar(4) NOT NULL default 'USA',
  `contact` varchar(40) NOT NULL default '',
  `phone` varchar(17) NOT NULL default '',
  `fax` varchar(17) NOT NULL default '',
  `e_mail` varchar(50) NOT NULL default '',
  `grower_sw` char(1) NOT NULL default 'Y',
  `vendor_sw` char(1) NOT NULL default 'N',
  `owner_sw` char(1) NOT NULL default 'N',
  `ship_sw` char(1) NOT NULL default 'N',
  `notes` text NOT NULL,
  PRIMARY KEY  (`company_num`),
  KEY `company_name` (`name`),
  KEY `company_zip` (`zip`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `company`
-- 

INSERT INTO `company` (`company_num`, `name`, `addr1`, `addr2`, `city`, `state`, `zip`, `country`, `contact`, `phone`, `fax`, `e_mail`, `grower_sw`, `vendor_sw`, `owner_sw`, `ship_sw`, `notes`) VALUES (1, 'Dawnridge', '2864 Dawnridge', '', 'Redding', 'CA', '96001', 'USA', 'Dan The Man', '111-222-3333', 'fax', 'dknoware@netscape.net', 'Y', 'Y', 'Y', 'Y', ''),
(2, 'Dawnridge', '2864 Dawnridge', '', 'Redding', 'CA', '96001', 'USA', 'Dan The Man', '111-222-3333', 'fax', 'dknoware@netscape.net', 'Y', 'Y', 'Y', 'Y', ''),
(3, 'Tetris Winery', '1234 Buger', '', 'Seattel', 'CA', '96001', 'USA', 'Ray the Pay', '111-222-3333', 'fax', 'dknoware@netscape.net', 'Y', 'Y', 'Y', 'Y', '');

-- --------------------------------------------------------

-- 
-- Table structure for table `wpo`
-- 

CREATE TABLE `wpo` (
  `wpo_num` decimal(6,1) unsigned NOT NULL default '0.0',
  `wpo_ctl` char(1) NOT NULL default 'P',
  `wpo_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `req_user_num` int(10) unsigned NOT NULL default '0',
  `ent_user_num` int(10) unsigned NOT NULL default '0',
  `chk_user_num` int(10) unsigned NOT NULL default '0',
  `cmp_user_num` int(10) unsigned NOT NULL default '0',
  `wpo_op` char(2) NOT NULL default '',
  `load_cent_id` varchar(5) NOT NULL default '',
  `run_min` int(11) NOT NULL default '0',
  `num_from` tinyint(2) NOT NULL default '0',
  `num_to` tinyint(2) NOT NULL default '0',
  `contract_id` varchar(10) NOT NULL default '',
  `company_num` int(10) unsigned NOT NULL default '0',
  `gross_pay` decimal(10,2) NOT NULL default '0.00',
  `reward_pay` decimal(10,2) NOT NULL default '0.00',
  `penalty_pay` decimal(10,2) NOT NULL default '0.00',
  `net_pay` decimal(10,2) NOT NULL default '0.00',
  `notes` text NOT NULL,
  `user_num` int(11) NOT NULL default '0',
  PRIMARY KEY  (`wpo_num`),
  UNIQUE KEY `wpo_op` (`wpo_op`,`wpo_datetime`),
  UNIQUE KEY `wpo_comp` (`wpo_datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `wpo`
-- 

INSERT INTO `wpo` (`wpo_num`, `wpo_ctl`, `wpo_datetime`, `req_user_num`, `ent_user_num`, `chk_user_num`, `cmp_user_num`, `wpo_op`, `load_cent_id`, `run_min`, `num_from`, `num_to`, `contract_id`, `company_num`, `gross_pay`, `reward_pay`, `penalty_pay`, `net_pay`, `notes`, `user_num`) VALUES (1.0, 'R', '2006-03-11 05:43:00', 0, 0, 0, 0, 'C', '', 19, 1, 1, 'contract_1', 1, 0.00, 0.00, 0.00, 0.00, '', 0),
(2.0, 'R', '2006-03-11 05:46:00', 0, 0, 0, 0, 'C', '', 19, 1, 1, 'contract_2', 1, 0.00, 0.00, 0.00, 0.00, '', 0),
(3.0, 'R', '2006-03-11 05:49:00', 0, 0, 0, 0, 'C', '', 19, 1, 1, 'contract_3', 2, 0.00, 0.00, 0.00, 0.00, '', 0),
(4.0, 'R', '2006-03-11 05:50:00', 0, 0, 0, 0, 'C', '', 19, 1, 1, 'contract_4', 1, 0.00, 0.00, 0.00, 0.00, '', 0),
(5.0, 'R', '2006-03-11 05:51:00', 0, 0, 0, 0, 'C', '', 19, 1, 1, 'contract_5', 1, 0.00, 0.00, 0.00, 0.00, '', 0);

========================================================
Thanks for the help..
Have a great day...
Dan Kokenge

sorry I meant the mysql developers should please speed up and release a patch (=diff) for this so I can fix this immediately.

What version did you use before? Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/join.html:

"Further changes in join processing were made in 5.0.12 to make MySQL more compliant with standard SQL. These charges are described later in this section.
...

 However, the precedence of the comma operator is less than than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.
...
"

So, it is expected and documented behaviour now. Sorry.