Re: Cartesian product bug?

Greg Stark wrote:
>
> "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
>
> > On Fri, 31 Oct 2003, Tom Lane wrote:
> >
> > > (Personally I think NATURAL JOIN is an evil, bug-prone construct,
> > > precisely because coincidental matches of column names will mess up your
> > > results.)
> >
> > Me too. When I first saw it, I figured it would "naturally join" the two
> > tables on their fk/pk relation if there was one. That seems natural.
> > Joining on two fields that just happen to have the same name is unnatural
> > to me.
>
> Well 99% of the time I impose on myself a constraint to only use the same name
> iff they refer to the same attribute. So if they have the same name then they
> really ought to be a reasonable join clause.
>
> However the 1% are things like "date_created, date_updated" or even flags like
> "active", "deleted" etc. Which are more than enough to make it utterly
> useless.
>
> Too bad really, it would be a handy thing for ad-hoc queries typed at psql. It
> would still seem too fragile for production queries though.

I think the reason they don't use pk/fk in natural joins is because you can
join all sorts of results, like SELECT in FROM, that doesn't always have
a meaningful pk/fk.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073