MySQL 5.0 LEFT JOINs not working correctly

Dev Shed Forums Sponsor:

October 31st, 2005, 04:05 PM

bauhsoj

Contributing User

Join Date: Aug 2005

Posts: 215

Time spent in forums: 1 Day 10 h 55 m 23 sec
Reputation Power: 4

MySQL 5.0 LEFT JOINs not working correctly

I recently upgraded from MySQL 4.1 to 5.0.15. However, to my suprise I have discovered that certain LEFT JOINs do not work anymore.

For instance, the error #1054 is produced with the message Unknown column 'items.item_id' in 'on clause' when the following left join is executed:

Code:

SELECT items.item_id, items.title, md.keywords, cat.category
FROM items_table AS items, meta_data_table AS md, categories_table AS cat
LEFT JOIN restricted_table AS rst ON items.item_id=rst.item_id
WHERE items.title LIKE 'the%'
     AND md.item_id=items.item_id
     AND cat.cat_id=items.cat_id
     AND rst.item_id IS NULL
GROUP BY items.item_id
ORDER BY items.title DESC
LIMIT 10

This query worked fine in MySQL 4.1 and the current 5.0 documentation doesn't seem to indicate that it shouldn't.

Strangely I have found that removing all references to "meta_data_table" and "categories_table" cause the error Unknown column 'items.item_id' in 'on clause' to stop occurring. I haven't a clue why, seeing as the error message mentions nothing of these other tables. Additionally, removing those tables from the query is not an option in any case due to the fact that they retrieve essential information.

My server specs are as follows: Windows XP SP2, Apache 2.0.54, and PHP 5.0.4 with an AMD 64 processer.

Anyone have any ideas what could be wrong here?

October 31st, 2005, 04:40 PM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,067

Time spent in forums: 2 Months 4 Weeks 2 h 10 sec
Reputation Power: 281

Quote:

SELECT items.itemid, items.title, md.keywords, cat.category
FROM items_table AS items, meta_data_table AS md, categories_table AS cat
LEFT JOIN restricted_table AS rst ON items.item_id=rst.item_id

maybe ...

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

October 31st, 2005, 05:04 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 18,320

Time spent in forums: 1 Month 4 Weeks 1 Day 23 h 39 m 36 sec
Reputation Power: 1084

i am guessing, but your problems might just go away if you rewrite the query completely using JOIN syntax

it's possible the optimizer may be getting confused because you are mixing a LEFT JOIN with old-school table list joins where the conditions are in the WHERE clause

this might be a new bug, but who cares -- if it's not working, you want to find a workaround, not sit and wait for a bug to be resolved

in any case you shouldn't've written the query with that mix of styles in 4.1 either

in addition, you have another potentially serious problem in that your GROUP BY is invalid, it should really contain all the non-aggregate columns in the SELECT

so try this --

Code:

SELECT items.itemid
     , items.title
     , md.keywords
     , cat.category
  FROM items_table AS items
INNER
  JOIN meta_data_table  AS md
    ON items.item_id     = md.item_id
INNER
  JOIN categories_table AS cat
    ON items.cat_id      = cat.cat_id
LEFT OUTER
  JOIN restricted_table AS rst 
    ON items.item_id     = rst.item_id
 WHERE items.title LIKE 'the%'
   AND rst.item_id IS NULL
GROUP 
    BY items.itemid
     , items.title
     , md.keywords
     , cat.category
ORDER 
    BY items.title DESC LIMIT 10

__________________
r937.com | rudy.ca

pre-order my book Simply SQL from Amazon

October 31st, 2005, 05:52 PM

bauhsoj

Contributing User

Join Date: Aug 2005

Posts: 215

Time spent in forums: 1 Day 10 h 55 m 23 sec
Reputation Power: 4

Quote:

Originally Posted by r937

in addition, you have another potentially serious problem in that your GROUP BY is invalid, it should really contain all the non-aggregate columns in the SELECT

Having to do that could actually cause a problem. That is the simplest out of several key queries that are used throughout the site. Some of the more complex queries utilize about 36 different columns across multiple tables, query formatted assignments, and IF() conditional assignments performed by the database engine. Would I need to put all of those columns in the GROUP BY statement?

Also, the items_table actually has columns for 8 different cat_id's (e.g. cat_id1, cat_id2, cat_id3, etc.) so how would I work that into the query if I needed cat.cat_id IN(items.cat_id1, items.cat_id2, .... etc.)?

I also need to be certain that the resultant query will work on a server running MySQL 4.0. MySQL 5.0 is run on our test server and it could be some time before the live server is running version 5.

October 31st, 2005, 05:55 PM

bauhsoj

Contributing User

Join Date: Aug 2005

Posts: 215

Time spent in forums: 1 Day 10 h 55 m 23 sec
Reputation Power: 4

Quote:

Originally Posted by pabloj

maybe ...

Just a typo on my part when making this post. Sorry.

October 31st, 2005, 06:25 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 18,320

Time spent in forums: 1 Month 4 Weeks 1 Day 23 h 39 m 36 sec
Reputation Power: 1084

Quote:

Originally Posted by bauhsoj

Having to do that could actually cause a problem.

okay, your call, but i would suggest you read the page in the mysql docs about "group by hidden fields" where it says you can get unpredictable results

Quote:

Originally Posted by bauhsoj

Also, the items_table actually has columns for 8 different cat_id's (e.g. cat_id1, cat_id2, cat_id3, etc.) so how would I work that into the query if I needed cat.cat_id IN(items.cat_id1, items.cat_id2, .... etc.)?

the syntax is fine, but i bet you end up with one-to-many cross join effects

November 3rd, 2005, 01:34 AM

mpedrummer

Contributing User

Join Date: Jan 2004

Posts: 201

Time spent in forums: 15 h 31 m 49 sec
Reputation Power: 6

I have the solution to this, but not (all of) the reasoning behind it.

MySQL 5 supports SQL2003 more strictly. The FROM section of your query should be enclosed in parenthesis () in order to have the behavior you expect from 4.1, etc. MySQL 5 interprets your initial query (from the first post) to mean that you are LEFT JOINing to the final table specified in your FROM clause, not all of them, which was the behavior in 4.x. By enclosing the FROM in parenthesis, you're indicating to the parser that you wish to LEFT JOIN against any table that's already been specified.

This was poorly documented, and it took me a while to find it.

MPEDrummer

Comments on this post

pabloj agrees!

Last edited by mpedrummer : November 3rd, 2005 at 01:36 AM.

November 3rd, 2005, 06:22 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 18,320

Time spent in forums: 1 Month 4 Weeks 1 Day 23 h 39 m 36 sec
Reputation Power: 1084

Quote:

Originally Posted by mpedrummer

This was poorly documented, and it took me a while to find it.

please do us a favour and show us where

November 3rd, 2005, 06:29 AM

mpedrummer

Contributing User

Join Date: Jan 2004

Posts: 201

Time spent in forums: 15 h 31 m 49 sec
Reputation Power: 6

Here you go

Look at the comment by [8 Oct 12:12] Sergei Golubchik

It's not really documentation, but it's the best I could find, and it turned out to be right

MPEDrummer

/edit - I coulda sworn they parsed links automatically...

#10

November 3rd, 2005, 07:07 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 18,320

Time spent in forums: 1 Month 4 Weeks 1 Day 23 h 39 m 36 sec
Reputation Power: 1084

thanks, that's a great exlanation

to quote from that page --

Quote:

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).

yep, that's a "workaround"

in my opinion it would be better to rewrite the joins using JOIN syntax, like in post #3

#11

November 3rd, 2005, 08:31 AM

Guelphdad

Headed to unemployment

Dev Shed God 5th Plane (7000 - 7499 posts)

Join Date: Nov 2001

Location: Guelph Canada

Posts: 7,192

Time spent in forums: 2 Months 2 Weeks 6 h 14 m 34 sec
Reputation Power: 629

Quote:

Originally Posted by r937

thanks, that's a great exlanation

to quote from that page --yep, that's a "workaround"

in my opinion it would be better to rewrite the joins using JOIN syntax, like in post #3

I concur completely, there is no real reason to mix the two types of join syntax, the list type of join can get mixed up quite easily when you leave out a join condition in the where clause, or better yet where you have to do a "workaround" as above.

__________________
MySQL - reformat varchar dates to proper date type || MySQL - room vacancies || output results with column names with mysql only
Latest row per group || Deleting duplicates || Gaps in auto increment column
My site: sql help articles

#12

November 3rd, 2005, 11:28 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,807

Time spent in forums: 2 Weeks 5 Days 9 h 23 m 14 sec
Reputation Power: 48

Code:

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

will not work in most other DBMS since it is not valid SQL as defined in the standard.

Comments on this post

Guelphdad agrees: Standards this, standards, that, blah, blah, blah. Oh wait I agree with that!