Thread: swap relations to be able to execute a left join
Hi there, The other day, a coworker who loves mysql was complaining to me because he had a hard time to find out why this query didn't work on PostgreSQL: SELECT AR.artifact_id, AT.type_nm, AR.title, U.dept, FROM Artifact_Revisions AR, Revisions_to_Types RTT, Artifact_Types AT LEFT JOIN Users U on (U.user_id = AR.principal_user_id) WHERE AR.revision_id = RTT.revision_id AND RTT.type_id = AT.type_id AND AR.revision_id = 28403; Apparently, once he swapped the 1st (AR) and the 3rd (AT) relation in the FROM, the query worked. He took the opportunity to tell how much he doesn't like PostgreSQL and how much he loves mysql. Apparently, he told me (I didn't confirm it) in mysql you wouldn't need to swap the order of the relations to execute the query. I am sure there is a good reason why you have to swap the relations in PostgreSQL. Anyone? Thanks, David
On Fri, Dec 09, 2005 at 09:16:15AM -0800, David Rio Deiros wrote: > The other day, a coworker who loves mysql was complaining to me because > he had a hard time to find out why this query didn't work on PostgreSQL: ... > FROM > Artifact_Revisions AR, > Revisions_to_Types RTT, > Artifact_Types AT > LEFT JOIN > Users U > on (U.user_id = AR.principal_user_id) ... > Apparently, once he swapped the 1st (AR) and the 3rd (AT) relation in > the FROM, the query worked. ... > I am sure there is a good reason why you have to swap the relations > in PostgreSQL. Anyone? I should think his query is joining something other than what he *thinks* it does... Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 12/9/05, David Rio Deiros <driodeiros@gmail.com> wrote: > Hi there, > > The other day, a coworker who loves mysql was complaining to me because > he had a hard time to find out why this query didn't work on PostgreSQL: > [..select..] > FROM > Artifact_Revisions AR, > Revisions_to_Types RTT, > Artifact_Types AT > LEFT JOIN > Users U > on (U.user_id = AR.principal_user_id) [..where..] > > Apparently, once he swapped the 1st (AR) and the 3rd (AT) relation in > the FROM, the query worked. > [..snip..] > > I am sure there is a good reason why you have to swap the relations > in PostgreSQL. Anyone? > Because PostgreSQL is not broken... What i see in this SELECT is an LEFT JOIN between AT and U (note that the other relations in the FROM are separated by commas so they have nothing to do with LEFT JOIN) and is using a JOIN clause from a relation that has nothing to do with the LEFT JOIN... This is just a bug in MySQL and i see no way to say it's a feature... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
Jaime Casanova <systemguards@gmail.com> writes: > On 12/9/05, David Rio Deiros <driodeiros@gmail.com> wrote: >> I am sure there is a good reason why you have to swap the relations >> in PostgreSQL. Anyone? > Because PostgreSQL is not broken... > What i see in this SELECT is an LEFT JOIN between AT and U (note that > the other relations in the FROM are separated by commas so they have > nothing to do with LEFT JOIN) and is using a JOIN clause from a > relation that has nothing to do with the LEFT JOIN... Right. MySQL apparently thinks that JOIN has the same precedence as comma in a FROM-list, but anyone who has bothered to read the SQL standard knows that JOIN is supposed to bind tighter than comma. Your coworker is depending on a flat-out-incorrect behavior of MySQL. regards, tom lane
On Fri, Dec 09, 2005 at 12:47:19PM -0500, Tom Lane wrote: > Right. MySQL apparently thinks that JOIN has the same precedence as > comma in a FROM-list, but anyone who has bothered to read the SQL > standard knows that JOIN is supposed to bind tighter than comma. > Your coworker is depending on a flat-out-incorrect behavior of MySQL. Hmm...I get errors in MySQL 5.0.16 with the query as posted so I have to wonder what the real query was. With no changes at all the query fails due to an extra comma at the end of the select list: mysql> SELECT -> AR.artifact_id, -> AT.type_nm, -> AR.title, -> U.dept, -> FROM -> Artifact_Revisions AR, -> Revisions_to_Types RTT, -> Artifact_Types AT -> LEFT JOIN -> Users U -> on (U.user_id = AR.principal_user_id) -> WHERE -> AR.revision_id = RTT.revision_id -> AND RTT.type_id = AT.type_id -> AND AR.revision_id = 28403; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versionfor the right syntax to use near 'FROM Artifact_Revisions AR, Revisions_to_Types RTT, Artifact_Types A' at line 6 If I remove the offending comma then I get an error similar to what PostgreSQL would give: mysql> SELECT -> AR.artifact_id, -> AT.type_nm, -> AR.title, -> U.dept -> FROM -> Artifact_Revisions AR, -> Revisions_to_Types RTT, -> Artifact_Types AT -> LEFT JOIN -> Users U -> on (U.user_id = AR.principal_user_id) -> WHERE -> AR.revision_id = RTT.revision_id -> AND RTT.type_id = AT.type_id -> AND AR.revision_id = 28403; ERROR 1054 (42S22): Unknown column 'AR.principal_user_id' in 'on clause' Swapping AR and AT as suggested finally works (the test tables are empty so the query returns no results): mysql> SELECT -> AR.artifact_id, -> AT.type_nm, -> AR.title, -> U.dept -> FROM -> Artifact_Types AT, -> Revisions_to_Types RTT, -> Artifact_Revisions AR -> LEFT JOIN -> Users U -> on (U.user_id = AR.principal_user_id) -> WHERE -> AR.revision_id = RTT.revision_id -> AND RTT.type_id = AT.type_id -> AND AR.revision_id = 28403; Empty set (0.00 sec) This leaves me wondering what the test case really was or if MySQL behaves differently under different versions (I'm aware of the sql_mode setting but it's empty on my system; I haven't checked if any of its possible values would affect precedence in the from list). -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes: > On Fri, Dec 09, 2005 at 12:47:19PM -0500, Tom Lane wrote: >> Your coworker is depending on a flat-out-incorrect behavior of MySQL. > Hmm...I get errors in MySQL 5.0.16 with the query as posted so I > have to wonder what the real query was. (tries it...) After removing the bogus comma, MySQL 4.1.14 swallows the query without complaint, so it would seem that MySQL finally fixed this bug at 5.0. I wonder what the OP's coworker will say when he tries to migrate his code to 5.0 ... regards, tom lane
On Fri, Dec 09, 2005 at 02:17:59PM -0500, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > Hmm...I get errors in MySQL 5.0.16 with the query as posted so I > > have to wonder what the real query was. > > (tries it...) After removing the bogus comma, MySQL 4.1.14 swallows > the query without complaint, so it would seem that MySQL finally fixed > this bug at 5.0. I wonder what the OP's coworker will say when he tries > to migrate his code to 5.0 ... Here we go: http://dev.mysql.com/doc/refman/5.0/en/join.html "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3))." -- Michael Fuhr
On Fri, Dec 09, 2005 at 12:29:02PM -0700, Michael Fuhr wrote: > On Fri, Dec 09, 2005 at 02:17:59PM -0500, Tom Lane wrote: > > Michael Fuhr <mike@fuhr.org> writes: > > > Hmm...I get errors in MySQL 5.0.16 with the query as posted so I > > > have to wonder what the real query was. > > > > (tries it...) After removing the bogus comma, MySQL 4.1.14 swallows > > the query without complaint, so it would seem that MySQL finally fixed > > this bug at 5.0. I wonder what the OP's coworker will say when he tries > > to migrate his code to 5.0 ... > > Here we go: > > http://dev.mysql.com/doc/refman/5.0/en/join.html > > "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same > precedence, so the join expression t1, t2 JOIN t3 was intrepreted as > ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is > interpreted as (t1, (t2 JOIN t3))." Michael, Jaime, Tom, thanks for you answers. Sorry for the bogus comma. The query had initially more fields in the select but I just removed them to make the query smaller. I missed to remove that comma. This is what I am going to send to my coworker: I think you may want to be interested in the reply that I am going to send to my coworker: ----------- Dear Mr I love mysql, Your query worked in mysql (versions prior to 5.0.12) because Mysql was not following the sql standard. So you were relying in a bug and not in a feature. Luckily that bug has been fixed in the latest mysql release: http://dev.mysql.com/doc/refman/5.0/en/join.html "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3))." --------- Thanks again, David