Re: Cartesian product bug?

=?utf-8?Q?=C3=85smund_Kveim_Lie?= <asmundkl(at)skipthis(dot)ifi(dot)uio(dot)no> writes:

> SELECT * FROM a,b NATURAL JOIN c;

> SELECT * FROM a CROSS JOIN b NATURAL JOIN c;

> These two example queries should give the same result.

No, they shouldn't, because JOIN binds more tightly than comma. The
first is equivalent to

SELECT * FROM a CROSS JOIN (b NATURAL JOIN c);

while in the second case the JOINs associate left-to-right, giving

SELECT * FROM (a CROSS JOIN b) NATURAL JOIN c;

Because you have columns with the same names in A and C, the second
NATURAL JOIN has a different implicit join clause than the first.

(Personally I think NATURAL JOIN is an evil, bug-prone construct,
precisely because coincidental matches of column names will mess up your
results.)

> In the first query, it seems like its doing the natural
> join between b and c, and then does the Cartesian product on that
> result with a. On the second query, it does as we assume it should,
> namely does the Cartesian product first.

I think your expectations have been set by MySQL, which last I heard
interprets all joins as being done left-to-right. That's not compliant
with the SQL standard, however.

regards, tom lane