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

Bug #13551	#1054 - Unknown column 'xxxx' in 'on clause'
Submitted:	28 Sep 2005 4:39	Modified:	31 Oct 2005 18:34
Reporter:	Christopher Vu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.0.13 RC	OS:	Windows (Windows XP)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
LEFT JOIN gives the "#1054 - Unknown column 'xxxx' in 'on clause'" error on MySQL 5, but it's working fine in MySQL 4.1

How to repeat:
CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `type` enum('a','b') default NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t1` VALUES (1, 'a'), (2, 'a'), (3, 'b'), (4, 'a'), (5, 'b');

CREATE TABLE IF NOT EXISTS `t2` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t2` VALUES (1), (1), (1), (2), (2);

CREATE TABLE IF NOT EXISTS `t3` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t3` VALUES (1), (1), (2), (2), (3);

#Test 1
select t1.id
from t1, t2
left join t3 on t1.id = t3.t1_id and t1.type = 'a'
where t1.id = t2.t1_id
group by t1.id
#1054 - Unknown column 't1.id' in 'on clause'

#Test 2, swap the order of the conditions in "on"
select t1.id
from t1, t2
left join t3 on t1.type = 'a' and t1.id = t3.t1_id
where t1.id = t2.t1_id
group by t1.id
#1054 - Unknown column 't1.type' in 'on clause'

Thanks for the bug report. I tested on  5.0.14.

mysql> select t1.id
    -> from t1, t2
    -> left join t3 on t1.id = t3.t1_id and t1.type = 'a'
    -> where t1.id = t2.t1_id
    -> group by t1.id;
ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause'
mysql>

I tested on 4.1.15. There is not any problem.

Hi.
I'm using Linux and I'm having the same kind of problem.

SELECT DISTINCT SQL_CALC_FOUND_ROWS
					 ifnull(cs.ConsultantID,ps.ConsultantID) as CommissionConsultantID,
					ps.ProductSaleID,
					psc.CustomerID,
					bu.Name as CustomerName,
					p.ProductID,
					p.InterfaceType,
					IF(p2.Name IS NULL, p.Name, concat(p2.Name,' - ', p.Name)) as ProductName,
					ps.DateContracted,
					ps.DateDeadline,
					ps.Title as ProductSaleTitle,
					ps.StatusID,
					IFNULL(pst.ProductStatus,'Desconhecido') as ProductStatus,
					p.RequiredCustomerTypes
				FROM
					ProductSales			ps,
					Products				p,
					ProductSalesCustomers	psc,
					Relationships			r,
					Customers				c,
					Businesses				bu
				LEFT JOIN Products		p2 	on	p.ParentProductID	= p2.ProductID
				LEFT JOIN ProductStatuses	pst	on	ps.StatusID		= pst.ProductStatusID
				LEFT JOIN Payments		pay	on	ps.ProductSaleID	= pay.ProductSaleID
				LEFT JOIN CommissionShare	cs	on	pay.PaymentID		= cs.PaymentID  and cs.ConsultantID = '106577' 
				LEFT JOIN Users			fuc	on	fuc.UserID		= cs.ConsultantID
				LEFT JOIN Users			fup	on	fup.UserID		= ps.ConsultantID
				LEFT JOIN UserProducts		up	on	up.DeniedProductID 	= p.ProductID and up.ConsultantID = 106577
				WHERE
					ps.ProductID		= p.ProductID and
					ps.ProductSaleID	= psc.ProductSaleID and
					psc.CustomerID		= r.CustomerID and
					r.SecondCustomerID	= c.CustomerID and
					c.CustomerID		= bu.CustomerID and
					ps.StatusID			> 0 and
					(cs.FranchiseID		= 409330 or
					fuc.FranchiseID		= 409330 or
					fup.FranchiseID		= 409330)
					 and ifnull(cs.ConsultantID,ps.ConsultantID) = '106577'  and DateContracted >= '2004-07-05 0:0:00' and DateContracted <= '2005-11-03 23:59:00'
				ORDER BY
					bu.Name
				LIMIT 0,50
-- Unknown column 'p.ParentProductID' in 'on clause'

When I try the same "left join" in a smaller query (with phpMyAdmin)...

SELECT DISTINCT
SQL_CALC_FOUND_ROWS *
FROM Products p
LEFT JOIN Products p2 ON p.ParentProductID = p2.ProductID
-- Showing rows 0 - 29 (46 total, Query took 0.0041 sec)

The same query was working fine on 5.0.11-beta (on the same machine), and it's working very well with 4.1.13-log, in my production server (FreeBSD)

Cheers!

I have the same problem with version 5.0.13 RC in Windows XP and Linux Ubuntu.

The query is:

............................................................................................................

SELECT f.facultat, p.TU, p.DNI, p.NIU, p.nom, p.cognom1, p.cognom2, ts.tipus as tipus_sexe,p.telefon, p.mobil, tv.abreviatura as tipus_via, p.direccio, p.codi_postal, p.poblacio, p.provincia, p.pais, p.email, date_format(p.data_naix,'%d/%m/%Y') as data_naix, p.curs, p.cicle,  p.estudis,	tu.tipus as tipus_usuari, d.departament, e1.esport as interes1, e2.esport as interes2, e3.esport as interes3, p.observacions 

FROM participants p, tipus_usuari tu, tipus_via tv, tipus_sexe ts

LEFT JOIN facultats f ON p.id_facultat=f.id	
LEFT JOIN departaments d ON p.id_departament=d.id
LEFT JOIN esports e1 ON p.interes1=e1.id 
LEFT JOIN esports e2 ON p.interes2=e2.id
LEFT JOIN esports e3 ON p.interes3=e3.id 

WHERE ts.id=p.id_tipus_sexe AND tv.id=p.id_tipus_via AND tu.id=id_tipus_usuari
AND p.id=10

............................................................................................................

In other server with version 5.0.11-beta-nt on Windows 2003 Server Standard works fine.

5.0.12-beta have the same problem.
5.0.11-beta works fine.

Is it the same bug that this http://bugs.mysql.com/13597 ???

If it's true, it has solved in version 5.0.15

Just chiming in to say I am having this problem on a simple query that worked perfectly well in 4.X but breaks in  5.0.13-rc:

SELECT thread.threadid, thread.forumid
	FROM thread AS thread, subscribethread AS subscribethread
	LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
	WHERE subscribethread.threadid = thread.threadid
	AND subscribethread.userid = 345
	AND thread.visible = 1
	AND lastpost > 1128589382
	AND deletionlog.primaryid IS NULL

This query has been running multiple versions of vBulletin for years without problem.

This is a change that was made in 5.0.15 to make MySQL more compliant with the standard.
According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
    <table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
    <table factor>
  | <joined table>
<joined table> ::=
    <cross join>
  | <qualified join>
  | <natural join>
...

Thus when you write

  ... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1)    ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2)    ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1 columns from expr.

Unfortunately, this change is not properly documented in the manual, it will be fixed.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The change in behaviour described by Sergei actually occurred in 5.0.12 and was already documented in the 5.0.12 changelog.

I have also updated the SQL Syntax chapter in the 5.0 and 5.1 manuals with this info.

Following discussion with Sergei: 

1. There were TWO different changes in the processing of joins to bring MySQL's behaviour in line with that of SQL:2003. 

2. The behaviour change reported by C. Vu is NOT a bug; it is entirely intentional. Queries that used to work with "FROM t1, t2" now need to be written using "FROM (t1, t2)" as previously stated. 

3. However, the Manual's explanation as to why this is so in this particular case is incorrect, and will be fixed ASAP.

Reassigned this bug to Paul at his suggestion.

On Server version:  "5.0.4-beta-Max / Ver 14.12 Distrib 5.0.13-rc, for unknown-linux-gnu (x86_64) using readline 5.0" I get the following as a result for this query:

select distinct nhb_group_member.is_active, nhb_group_member.group_id, nhb_group_member.has_mojo, A.group_owner, A.group_name, A.description, A.active_date, A.expire_date, A.is_public, B.group_name as 'group_owner_name' from nhb_group_member, nhb_group as A, nhb_group as B right join nhb_group on A.group_owner = B.group_id where A.group_id = nhb_group_member.group_id and nhb_group_member.user_id = 'PER06262000.000000000001'

-------+-------------+-----------+------------------+
| t         | GRO06262000.000000000016 | f        | GRO06262000.000000000003 | cg.novahead           | Custom user group               | 2001-07-11 00:00:00 | NULL        | t         | xpo_sys.user     |
| t         | GRO06272000.000000000008 | f        | SYS00000000.000000000000 | xpo_svc.Admin         | Service user group              | 2002-09-19 13:46:58 | NULL        | f         | NULL             |
| t         | GRO06272000.000000000014 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Welcome  | Application user group          | 2002-09-19 13:46:58 | NULL        | f         | NULL             |
| t         | GRO08282003.000000000001 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Data     | Data Application user group     | 2003-08-28 16:02:44 | NULL        | f         | NULL             |
| t         | GRO09022004.000000000002 | f        | GRO06262000.000000000000 | cg.novahead_create    | Novahead Creator Group          | 2004-09-01 00:00:00 | NULL        | f         | xpo_sys.create   |
| t         | GRO10032003.000000000001 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Company  | Company Application user group  | 2003-10-03 16:02:44 | NULL        | f         | NULL             |
| t         | GRO10062003.000000000001 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Help     | Help Application user group     | 2003-10-06 16:02:44 | NULL        | f         | NULL             |
| t         | GRO10072003.000000000001 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Main     | Main Application user group     | 2003-10-07 16:02:44 | NULL        | f         | NULL             |
| t         | GRO10152003.000000000001 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Person   | Person Application user group   | 2003-10-03 16:02:44 | NULL        | f         | NULL             |
| t         | GRO10242003.000000000001 | f        | SYS00000000.000000000000 | xpo_ag.Admin.Security | Security Application user group | 2003-10-24 16:02:44 | NULL        | f         | NULL             |
+-----------+--------------------------+----------+--------------------------+-----------------------+---------------------------------+---------------------+-------------+-----------+------------------+
10 rows in set (0.05 sec)

For the same query with the  same schema on "5.0.13-rc-Max  / Ver 14.12 Distrib 5.0.13-rc, for unknown-linux-gnu (x86_64) using readline 5.0" I'm getting: ERROR 1054 (42S22): Unknown column 'A.group_owner' in 'on clause'.

Some clarification would be appreciated.

Please don't make this just being a bug in the documentation.
I understand that this behaviour is by design, but it makes version 5 incompatible to version 4 and earlier.

Could a query be misinterpreted without parenthesis?

Is there a plan for a sql_mode setting (or somewhere else) that will give me the FROM clause parse semantics of version 4?

The changes in behavior for joins are documented here now,
along with suggestions on how to rewrite queries when necessary:

http://dev.mysql.com/doc/refman/5.0/en/join.html

There is no sql_mode setting for getting the old incorrect behavior.

I have similar problem in MySQL 5.0.33 on BSD System. It looks like this:

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `type` enum('a','b') default NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t1` VALUES (1, 'a'), (2, 'a'), (3, 'b'), (4, 'a'), (5, 'b');

CREATE TABLE IF NOT EXISTS `t2` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t2` VALUES (1), (1), (1), (2), (2);

CREATE TABLE IF NOT EXISTS `t3` (
  `t1_id` int(10) unsigned default NULL,

Error: 1054, Unknown column 't1.id' in 'on clause'
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t3` VALUES (1), (1), (2), (2), (3);

CREATE TABLE IF NOT EXISTS `t4` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t4` VALUES (1), (1), (2), (2), (3);

CREATE TABLE IF NOT EXISTS `t5` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t5` VALUES (1), (1), (1), (2), (2);

select t1.id as result
from t2, t1
left join t3 on t1.id = t3.t1_id, t4
left join t5 on t1.id = t5.t1_id
where
t1.`type` = 'a' and
t1.id = t2.t1_id and
t2.t1_id = t4.t1_id and
t5.t1_id = '1'

Error 1054, unknown column t1.id in on clause

There was wrong copy-paste in my previous comment, here is right sql:

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `type` enum('a','b') default NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t1` VALUES (1, 'a'), (2, 'a'), (3, 'b'), (4, 'a'), (5, 'b');

CREATE TABLE IF NOT EXISTS `t2` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t2` VALUES (1), (1), (1), (2), (2);

CREATE TABLE IF NOT EXISTS `t3` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t3` VALUES (1), (1), (2), (2), (3);

CREATE TABLE IF NOT EXISTS `t4` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t4` VALUES (1), (1), (2), (2), (3);

CREATE TABLE IF NOT EXISTS `t5` (
  `t1_id` int(10) unsigned default NULL,
  KEY `t1_id` (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t5` VALUES (1), (1), (1), (2), (2);

select t1.id as result
from t2, t1
left join t3 on t1.id = t3.t1_id, t4
left join t5 on t1.id = t5.t1_id
where
t1.`type` = 'a' and
t1.id = t2.t1_id and
t2.t1_id = t4.t1_id and
t5.t1_id = '1'

Error: 1054 Unknown column 't1.id' in 'on clause'

I have a Hibernate generated SQL which results in 1054 SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR) on MySQL V.5.051a. The cause is one inner join expresssion, which stands near other join expression. Interestingly, if I reorder the sequence of the join statements, the error is gone.

Here is the error causing statement:
select
        singleproj0_.PROJECT_NO as col_0_0_,
        singleproj0_.PROJECT_YEAR as col_1_0_,
        singleproj0_.PROJECT_BEGIN as col_2_0_,
        singleproj0_.PROJECT_END as col_3_0_,
        singleproj0_.STATUS as col_4_0_,
        institutio3_.INST_NUMBER as col_5_0_,
        institutio5_.NAME as col_6_0_,
        parties1_.PARTY_KIND as col_7_0_,
        featureite2_.NAME as NAME4_
    from
        dpjw.PROJECT singleproj0_
    left outer join
        dpjw.CENTRAL_PROJECT singleproj0_1_ 
            on singleproj0_.ID=singleproj0_1_.SINGLE_PRJ 
    inner join
        dpjw.PARTY parties1_ 
            on singleproj0_.ID=parties1_.PROJECT_ID,
        dpjw.INSTITUTION institutio3_,
        dpjw.CONTACTS institutio5_
    inner join
        dpjw.FEATUREITEM featureite2_
            on singleproj0_.STATUS=featureite2_.ID 
    where
        singleproj0_.DISCRIM='S' 
        and parties1_.INSTITUT_ID=institutio3_.ID 
        and institutio3_.CONTACT_ID=institutio5_.ID 
        and singleproj0_.PROJECT_YEAR=? 
    order by
        singleproj0_.PROJECT_NO

which results in " Unknown column 'singleproj0_.STATUS' in 'on clause'".

The reordered error free statement is this:

select
        singleproj0_.PROJECT_NO as col_0_0_,
        singleproj0_.PROJECT_YEAR as col_1_0_,
        singleproj0_.PROJECT_BEGIN as col_2_0_,
        singleproj0_.PROJECT_END as col_3_0_,
        singleproj0_.STATUS as col_4_0_,
        institutio3_.INST_NUMBER as col_5_0_,
        institutio5_.NAME as col_6_0_,
        parties1_.PARTY_KIND as col_7_0_,
        featureite2_.NAME as NAME4_
    from
        dpjw.PROJECT singleproj0_
    left outer join
        dpjw.CENTRAL_PROJECT singleproj0_1_ 
            on singleproj0_.ID=singleproj0_1_.SINGLE_PRJ 
    inner join
        dpjw.PARTY parties1_ 
            on singleproj0_.ID=parties1_.PROJECT_ID,
        dpjw.INSTITUTION institutio3_,
        dpjw.CONTACTS institutio5_
    inner join
        dpjw.FEATUREITEM featureite2_
            on singleproj0_.STATUS=featureite2_.ID 
    where
        singleproj0_.DISCRIM='S' 
        and parties1_.INSTITUT_ID=institutio3_.ID 
        and institutio3_.CONTACT_ID=institutio5_.ID 
        and singleproj0_.PROJECT_YEAR=2008 
    order by
        singleproj0_.PROJECT_NO

So, I think this is a bug that is not to be closed.

Sorry, my last statement is wrong; the no error causing SQL is:

select
        singleproj0_.PROJECT_NO as col_0_0_,
        singleproj0_.PROJECT_YEAR as col_1_0_,
        singleproj0_.PROJECT_BEGIN as col_2_0_,
        singleproj0_.PROJECT_END as col_3_0_,
        singleproj0_.STATUS as col_4_0_,
        institutio3_.INST_NUMBER as col_5_0_,
        institutio5_.NAME as col_6_0_,
        parties1_.PARTY_KIND as col_7_0_,
        featureite2_.NAME as NAME4_
    from
        dpjw.PROJECT singleproj0_
    left outer join
        dpjw.CENTRAL_PROJECT singleproj0_1_ 
            on singleproj0_.ID=singleproj0_1_.SINGLE_PRJ 
    inner join
        dpjw.FEATUREITEM featureite2_
            on singleproj0_.STATUS=featureite2_.ID 
    inner join
        dpjw.PARTY parties1_ 
            on singleproj0_.ID=parties1_.PROJECT_ID,
        dpjw.INSTITUTION institutio3_,
        dpjw.CONTACTS institutio5_
    
    where
        singleproj0_.DISCRIM='S' 
        and parties1_.INSTITUT_ID=institutio3_.ID 
        and institutio3_.CONTACT_ID=institutio5_.ID 
        and singleproj0_.PROJECT_YEAR=2008 
    order by
        singleproj0_.PROJECT_NO

We need to put parentheses or round brackets: ( ) around 
FROM tables and JOIN expressions and WHERE clause for mysql version 5.0 but its work fine without parentheses in mysql version 4.0

SELECT dd.directoryName AS subDirectoryName, d.directoryID, d.directoryName, p.title
FROM ( directory d, directoryRelationship dr, page p )
     LEFT JOIN directoryRelationship ddr ON ( ddr.parentDirectoryID = d.directoryID)
     LEFT JOIN directory dd ON ( dd.directoryID = ddr.childDirectoryID)
WHERE ( p.pageID = d.directoryPage)
      AND ( d.directoryID = dr.childDirectoryID)
      AND ( dr.parentDirectoryID =1)
      ORDER BY d.directoryID
      LIMIT 0 , 30 

enjoy mysql 

http://prabirchoudhury.wordpress.com

Following query has no problem when executed on MySql version 5.0.84 but giving error #1054 - Unknown column 'familynews.newsCatID' in 'on clause' when executed on 5.0.81

SELECT familynews.news_date, familynews.news, familymember.name, categ.category
FROM (familymember INNER JOIN familynews ON familymember.id = familynews.news_giver_id) INNER JOIN categ ON familynews.newsCatID = categ.id
ORDER BY familynews.news_date DESC , familymember.name, categ.category;

Any body knows how to fix the problem?

I am running a fairly old vesion.  Since I am a newbie to MySQL (and signed up to see if anyone knew which version fixed the problem or had a good workaround).  Saw that Amjad Sheikh saw it is fixed in a much more recent version.  I think 5.0.51a is the MySQL version I am using, but the complete server message is:
Server version: 5.0.51a-3ubuntu5.5 (Ubuntu)

Here is an ugly workaround, but it does SEEM to work.  At least it runs!
Not sure how well it will perform; testing is suggested.  But slow is better than nothing.

The key difference is copying the query text to both sides of a UNION which replaces the LEFT JOIN.  Basically, the query before UNION gets matches and the query after UNION gets the mismatches.  The indented lines should help you match up the similar text.  Sorry it is so long, but figured best not corrupt it by trying to shorten it.  Enjoy!

select I.item_id as ItemID, L.item_location_id as LocationItemID, 
S.store_name as StoreName, I.quantity as Quantity, I.name as ItemName, L.location as Location 
from shopping_list as I, shopping_store as S 
LEFT JOIN shopping_item_location as L 
    ON I.name = L.name 
   and I.user_id = L.user_id 
   and S.store_id = L.store_id 
where I.user_id = 1 and S.user_id = 1 
and I.list_id = 1 
and S.store_id = 1 
and I.quantity > 0 
order by L.location, UPPER(I.name)'

Resulted in:
Execute Failed: 1054 (Unknown column 'I.name' in 'on clause') at /usr/lib/cgi-bin/ShopList.cgi line 237.

This one seems to work:
select I.item_id as ItemID, L.item_location_id as LocationItemID, 
S.store_name as StoreName, I.quantity as Quantity, I.name as ItemName, L.location as Location 
from shopping_list as I, shopping_store as S, shopping_item_location as L 
where I.name = L.name 
  and I.user_id = L.user_id 
  and S.store_id = L.store_id 
and I.user_id = 1 and S.user_id = 1 
and I.list_id = 1 
and S.store_id = 1 
and I.quantity > 0 
UNION
select I.item_id as ItemID, -1 as LocationItemID, 
S.store_name as StoreName, I.quantity as Quantity, I.name as ItemName, ' ' as Location 
from shopping_list as I, shopping_store as S 
where not exists ( select * from shopping_item_location as L 
                   where I.name = L.name 
                     and I.user_id = L.user_id 
                     and S.store_id = L.store_id )
and I.user_id = 1 and S.user_id = 1 
and I.list_id = 1 
and S.store_id = 1 
and I.quantity > 0 
order by Location, UPPER(ItemName);

Sorry!!!  Thought to search for workaround and found the solution; I was missing some parentheses required by the newer version of MySQL.  The posting 8 Oct 2005 12:12] Sergei Golubchik solved my problem. Thanks, Sergei!

Maybe this information will help some of you.

I spent 1,5 day trying to figure out this error and finally someone else led me to the solution.

Check if there is a trigger on the table(s) you are using in your query which contains a reference to the unknown field.

In my case:
- Table A had a trigger referring to table B
- Renamed table B somewhere during the last week
- Every update or insert on table A returned error 1054.

The insert or update on table A was excecuted properly, but the trigger event returned the error message.