Re: Cartesian product bug?

Åsmund Kveim Lie <asmundkl@skipthis.ifi.uio.no> writes:

> SELECT * FROM a,b NATURAL JOIN c;

This parses as

select * from a, (b natural join c)

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

This parses as

select * from (a cross join b) natural join c

> These two example queries should give the same result. In the first query, it
> seems like it’s 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.
>
> Is this the correct behavior?

yes

You can put parentheses to change the explicit joins like

select * from a cross join (b natural join c);

But the implicit join is harder to fix. I think you either need to use an
explicit join like above or a subquery like

select * from (select * from a,b) as ab natural join c

I tend to find it's easier to stick to all explicit or all implicit joins and
not mix them. Personally I like explicit joins for aesthetic reasons
especially in 7.4 where they get optimized as well as implicit joins.


--
greg