New version of MYSQL will not parse valid select

Hi - I have two separate instances of MYSQL running on two seperate Verio VPS accounts (Lynux). The older one is running MYSQL version 5.0.51a.

The newer one was just created today. MYSQL on this is version 5.1.63. I loaded the exact same database as I was using on the older server - same name same tables same data. Now, certain simple selects work just fine but a more complex select fails.

To test I am making a connection to the MYSQL application through a Putty shell connection.

The error generated is not very helpful. It says "Unknown column 'x.y' in "on clause'". Obviously, the table x and the column y do exist and that is evident because the exact same select works on the other machine.

I am unable to offer any other clues other than the select is quite complex. It uses the term "LEFT OUTER JOIN" as well as two additional nested selects within the where clause. However, I removed both of these nested selects and still get the same error. If I keep stripping the select eventually I get a result but I am unable to determine the problem element.

Could this be some difference between the two versions?

you betcha

look at the ON clause, and you will notice that it references a table other than the ones immediately involved

this is a typical scenario --
the LEFT OUTER JOIN is supposed to join b and c, but the ON clause references a column from a

rewrite the query so that it uses explicit JOIN syntax only and removes any commas from the FROM clause

Thanks for the quick reply!

Your scenario is similar to my select. I suppose this is familiar to you. To strip my select down I have:

SELECT ...
FROM a, b, c
LEFT OUTER JOIN d
ON d.foo = b.bar

The error message would then reference b.bar.

Can you help me by showing me how to rewrite this select correctly?

Further testing indicates that I still may not be clear about your solution. Here is an actual select that I just ran on the problem machine and it works. Would seem to me that it shouldn't:

select * from parent, child left outer join prices on prices.childID=child.childID where parent.parentID=child.parentID limit 10;

Solved.

The correct syntax is:

SELECT ...
FROM a, c, b
LEFT OUTER JOIN d
ON d.foo = b.bar

IE it appears the table b needs to be reference last in the FROM clause because it also appears in the ON clause.

That seems to be a backwards step to me.

How would you handle this? Would this work?

SELECT ...
FROM a, c, b
LEFT OUTER JOIN d
ON d.foo = b.bar
LEFT OUTER JOIN e
ON e.foo = a.bar

i would much rather do this using your actual query

also, remember that you should remove those commas from the FROM clause, and replace them with INNER JOINs

No, the main issue here is that you are still using the old join syntax with commas in the FROM clause.

What Rudy meant with his:


Is that you should write your query with the newer standard explicit join syntax like:


The main reasons for this are:
1. It's the new standard
2. Much easier to read the query
3. There is almost no risk for you to forget a join condition and accidentally doing a cross join


The reason why it works when you place table b last in your "FROM a,c,b" is because the old ","-join syntax are in the newer versions interpreted last so basically your query looks like this:


Which would look like this if you had c last:

Which of course doesn't work since b is not known at the point when the ON condition is applied.

So to summarize, never use the comma syntax and you will be fine and you will write much better queries.

Thank you both. I'm self taught at php and mysql and it's certainly not my day job! Don't have time to keep up with the updates. The database is for an animal rescue web site that I volunteer my time. The animals thank you.