Query Fails On Mysql 5, Works On Mysql 4

Databases
1

Strange. I’m curious, what is it about this query that trips up mysql 5? I use:

  • Server version: 5.0.91-community

I notice there were bugs in earlier versions. Maybe my host just for this test server needs to update mysql 5?
http://bugs.mysql.com/bug.php?id=13551

Query Below:

SELECT [B]music_albums.*[/B],users.username,inventory.stock,inventory.upc_code,          DATE_FORMAT(release_date,'%M %d, %Y')  AS rel FROM  music_albums,users LEFT JOIN inventory ON ([B]inventory.album_id=music_albums.id[/B])   WHERE users.uid=music_albums.artist_id AND  music_albums.id='8'

This has worked perfectly on mysql 4. Now, with mysql 5+, it gives this error:

#1054 - Unknown column ‘music_albums.id’ in ‘on clause’

How does it not recognize the column music_albums.id ? What do I have to do differently, from now on, for mysql 5? I am testing our site on php 5/mysql 5 to make sure our site won’t break before we update our servers.

Thanks.

PS. Edit: I see it was a feature change and am reading the mysql docs but I think I need coffee to understand it…but for those going through the same thing, look at this page:

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

2

Fixed it with a lame query (for now);

SELECT music_albums.*,users.username,inventory.stock,inventory.upc_code, DATE_FORMAT(release_date,‘%M %d, %Y’) AS rel FROM music_albums,users,inventory WHERE users.uid=music_albums.artist_id AND music_albums.id=‘8’ limit 1

I hope this helps others who are stuck.

3

get out of the habit of using comma join syntax. stick specifically with INNER and OUTER joins. The problem you encounter is mysql 5 being more conforming with sql standards.
the comma takes precedence over the LEFT (OUTER) join and thus it can’t find the column inventory.album_id because it is only looking at music_albums and users and trying to perform that join first.


SELECT 
  music_albums.*,
  users.username,
  inventory.stock,
  inventory.upc_code,          
  DATE_FORMAT(release_date,'%M %d, %Y')  AS rel 
FROM  
  music_albums
INNER JOIN
  users 
ON
  users.uid=music_albums.artist_id
LEFT JOIN inventory 
ON 
  inventory.album_id=music_albums.id   
WHERE
  music_albums.id=8
4

Thank you for this. I have to re-wire my brain for mysql 5. It should be a tremendous learning experience.