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(at)gmail(dot)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(at)gmail(dot)com> writes:
> On 12/9/05, David Rio Deiros <driodeiros(at)gmail(dot)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 version for 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(at)fuhr(dot)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(at)fuhr(dot)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(at)fuhr(dot)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