On Sun, Oct 18, 2009 at 03:54:11PM -0700, Darren Duncan scratched on the wall:
> 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.
The definition of a Cartesian product is a bit more complete than "N rows
and M rows results in N * M rows." It is possible that a Cartesian
product may have been computed internally to arrive at these results,
but that does not change the fact that the result is not a Cartesian
product.
> 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);
Most likely, yes.
> ... but instead it produced the same result as this query:
>
> select t1.* from t as t1 cross join t as t2 using (c1,c2);
Yes. And the result of this query is not a Cartesian product either,
as you've thrown away half the columns.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
