what changed with mysql 5.0+ ??

sfullman

I've been coding for mysql 4.x for a long time and from experience with using a LEFT JOIN it doesn't matter where you place the left join in the from clause as long as you have an ON statement which refers to the joined table and some other table in the FROM clause.

OK, now here's the rub. The following query DOESN'T work in 5.0.27:

SELECT a.*, g.groupname, UNIX_TIMESTAMP(t.starttime) as starttime, UNIX_TIMESTAMP(t.endtime) as endtime FROM activity a, activity_group g LEFT JOIN activity_times t on a.activityid = t.activityid where a.groupid = g.groupid and a.confid = 91 ORDER BY g.order, a.groupid, t.starttime

NOW, look at the following query. it DOES work, but really the only difference is that I moved the reference to activity_group g "out of the way of " being between a and t:

SELECT a. *
FROM activity_group g, activity a
LEFT JOIN activity_times t ON a.activityid = t.activityid
WHERE a.groupid = g.groupid
AND a.confid =91
LIMIT 0 , 30

What gives here? It's pretty bad when I can understand the first query but 5.0 can't.. Is there any way to switch a setting in 5.0 to recognize the first query (and save me rewriting)??

Thank you, and if this is the standard now why? :-)

Samuel

bradgrafelman

Unless I'm missing something... in the first query, you join "activity_group" with "activity_times" whereas in the second query you're joining "activity" with "activity_times" ...

Since the "ON ..." statement compares 'a' and 't', the first query doesn't make sense.

Piranha

I don't know what the problem is, but it could have something to do with different ways of joining tables. You join a and g in the FROM and WHERE-statements and t in a LEFT JOIN. I suggest that you use INNER JOIN to join a and g.

sfullman

I'm not sure you understand what I'm asking. The two queries ARE essentially the same, and yes you're right I could use INNER JOIN vs. joining them in the where clause - but I don't wat to rewrite hundreds of queries.

my understanding of left join is that, ANYWHERE in the from clause, I can add:

LEFT JOIN newtable x on x.id = othertable.xid

(I made the fields up)

as long as othertable has been declared prior. If you look at the from and where clause of both queries in that light, they are essentially the same, only their order is different - does that now matter in 5.0?

Samuel

Piranha

I think that I understand what you are asking and why. The answer I posted was ment to help you solve the problem the best way I could. But I'll answer the questions as I interperate them without trying to help more than to just answer the questions:

Q: Why have it changed?
A: Don't know, ask on http://www.mysql.com/

Q: How should I do to be able to use my old queries?
A: Use the RDBMS it was developed in.

Q: I have updated to a new version of the RDBMS, is there any way that I can use this with my old queries?
A: Don't know, ask on http://www.mysql.com/

Piranha

Sorry about the post above, I was kind of irritated on other things when I wrote it. I still think that your questions could be better answered by the guys that develop mysql, but I should have written it that way and not the way I did.

Roger_Ramjet

Lots of new things in msql 5.

Most likely what new thing is affecting you is the ANSI sql_mode which makes mysql conform more to standard sql - which your queries definitly do not. Best to check current server mode and switch to TRADITIONAL.

Mysql always had sloppy sql syntax which is very different to that required by other RDBMS. Made it fast to write queries but made your code non-portable. Of course every vendor uses their own dialect of sql attempting to lock you in as much as help you out, but mysql seems to be the most deviant. Gets you into the wrong habits of coding.

sfullman

Ramjet, thx for providing a link to study.

regardless of whether it's "standard" sql or not, it only seems logical that you should be able to left join a table on x = y at any point in the from clause, and not just immediately after table to which you're joining it. I haven't checkd this out yet but I'm concerned about cases where, for various reasons, I repeatedly left join a table to the preceeding table on different criteria - urgh.

If I'm not mistaken, MySQL came out with the "LIMIT n, n" syntax @ the end of a query, which it used to drive me bananas using MSSQL because there was no quick and easy way to get a query subset like this. And I think LIMIT is now part of standard sql (but don't research these things :-)

Point is, I'm never critical of things that make sense, regardless of what the rules say.

Sxooter

Think about what this part of your query is saying:

FROM activity a, activity_group g LEFT JOIN activity_times t on a.activityid = t.activityid

From a parser perspective, it's saying to join activity_gourp g to activity_times t FIRST on a.activityid=t.activityid

Can you see the problem there? explicit join syntax is evaluated before where clause joins are evalutated, according to the SQL spec.

Before 5.0 MySQL would just do what it had to do to make this work, but not it follows the spec in this regard, and it's one of those things that's deeper than an ansi mode setting, because it changes the way the query parser works on a very low level and can't just be turned on and off.

Reverse those two tables and it should work.

FROM activity_group g, activity a LEFT JOIN activity_times t on a.activityid = t.activityid

But I highly recommend you move the query to an all join syntax:

FROM activity a
JOIN
activity_group g
on (a.groupid = g.groupid)
LEFT JOIN
activity_times t
on (a.activityid = t.activityid)

Now what you mean is obvious and your join syntax is much cleaner looking.

Note that some query optimizers might make bad decisions with explicit join syntax, so test it to be sure.