NATURAL INNER JOIN yields a cartesian product too. Just tried it!
> Date: Sun, 18 Oct 2009 15:54:11 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
>
> Jay A. Kreibich wrote:
> > On Sun, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched
> > on the wall:
> >> Clearly, SQLite executes a cartesian product!
> >
> > Look at the output. It does not produce a Cartesian product. All
> > the rows are valid:
> >
> > SQLite version 3.6.19
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> create table t (c1, c2);
> > sqlite> insert into t values ( 1, 2 );
> > sqlite> insert into t values ( 3, 4 );
> > sqlite> insert into t values ( 5, 6 );
> > sqlite> select * from t natural join t;
> > 1|2
> > 1|2
> > 1|2
> > 3|4
> > 3|4
> > 3|4
> > 5|6
> > 5|6
> > 5|6
> >
> > I'm not sure I'd call it correct, but it isn't a product.
>
> Jay, you've just proven Kristoffer's point. That result demonstrates that a
> cartesian product *was* produced. The table t had 3 rows, and the result had
> 3*3 rows, which is a cartesian product by definition.
>
> Your query should have produced the same result as this query:
>
> select t1.* from t as t1 inner join t as t2 using (c1,c2);
>
> ... but instead it produced the same result as this query:
>
> select t1.* from t as t1 cross join t as t2 using (c1,c2);
>
> Now I would be ready to consider that SQLite has a bug, but then looking at
> the
> syntax at http://sqlite.org/lang_select.html I see that SQLite defines
> multiple
> versions of natural join; it has *both* NATURAL INNER JOIN and NATURAL CROSS
> JOIN, and I'm guessing that if you leave the middle word out it is using
> CROSS
> by default, ostensibly for consistency for when you simply say JOIN.
>
> So if that is the case, then the current behavior is clearly documented as
> expected and so not an implementation bug. And so then you would have to say
> this:
>
> select * from t natural inner join t;
>
> ... to get the expected result of 3 rows.
>
> This all being said, the whole mess strikes me as a *design bug*. It simply
> doesn't make sense to have both NATURAL INNER and NATURAL CROSS syntax. One
> should simply be able to say NATURAL and it would do the right thing, which
> is a
> cartesian product when no column names are the same, an intersect when all
> column names are the same, and an inner join otherwise.
>
> My proposal is certainly logically sound. A natural join by definition only
> has
> a result row for each distinct pair of source rows that have the same values
> for
> the subset of their columns with the same names; a cartesian product is a
> degenerate case where that subset of columns has zero members, and so since
> the
> empty set matches the empty set every row from each source rowsets would
> match
> every row from the other rowsets.
>
> The only variations that make sense on a natural join is OUTER.
>
> -- Darren Duncan
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_________________________________________________________________
Windows Live: Gör det enklare för dina vänner att se vad du håller på med på
Facebook.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_2:092009
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
