This one beats the Hell out of me

[roopurt18]

There's a test server and live server.  Test server is MySQL Server version: 5.0.27-standard.  Live server is MySQL Server version: 4.1.22-standard-log.  The PHP code on both servers is identical.  A database was copied from the live server to the test server to troubleshoot a problem.

 

The particular web page is supposed to pull information from the database and just create a table.  On the live server a table is generated.  On the test server a message is generated saying that no documents could be found.  These documents are not stored on disk so that is not the problem.

 

Test Server Query

SELECT t.*, o.*, t.RevNum AS TrackRevNum FROM POOptions o, wsjob j,
IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t ON t.PONum=o.PONum WHERE
o.SubName=sn.IBSSubName AND sn.WVSubName='BBP' AND o.JobCode=j.job_code AND
j.pro_code='7795'

 

Live Server Query

SELECT t.*, o.*, t.RevNum AS TrackRevNum FROM POOptions o, wsjob j,
IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t ON t.PONum=o.PONum WHERE
o.SubName=sn.IBSSubName AND sn.WVSubName='BBP' AND o.JobCode=j.job_code AND
j.pro_code='7795'

 

Both queries are identical.  Copying either query into phpMyAdmin on the test server gives:

#1054 - Unknown column 'o.PONum' in 'on clause' 

 

Copying either query into phpMyAdmin on the live server gives 74 rows.  I've not checked that they're identical, but I'm pretty sure they are.

 

All four of the tables involved in the query have the same number of records according to phpMyAdmin, although I've not checked that they're identical.

 

Any thoughts?

[roopurt18]

The only thing I can think of is a bug [fix] in the MySQL 5.0.x client.

[Barand]

i'd write it like this and avoid the mix of join syntaxes

 

SELECT t.*, o.*, t.RevNum AS TrackRevNum

FROM POOptions o

    INNER JOIN wsjob j ON o.JobCode=j.job_code

    INNER JOIN IBSSubName2WVSubName sn ON o.SubName=sn.IBSSubName

    LEFT JOIN DocTrackingPOs t ON t.PONum=o.PONum

WHERE sn.WVSubName='BBP' AND j.pro_code='7795'

 

[roopurt18]

Well that seems to work.

 

Do you know why it is that makes the difference?  I only need enough information to research it further myself.

[Barand]

The only reason I can think of is that it gets bewildered by the mix of join methods (some using ON and others using WHERE)

 

I always prefer the ON syntax as it separates the join structure from the selection criteria and makes it easier to see what's going on.

 

I can't find the reference now but I'm sure I once read that ON syntax is more efficient.

[fenway]

Well that seems to work.

 

Do you know why it is that makes the difference?  I only need enough information to research it further myself.

The comma operator changed precedence in mysql 5, and is now lower than JOIN; so

 

FROM POOptions o, wsjob j, IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t

 

is being interpreted as

 

FROM POOptions o, wsjob j, ( IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t )

 

which is very much different.

 

[fenway]

bottom line -- never use the comma operator.  it's ambiguous, cryptic, and confusing.