Re: [HACKERS] Planner drops unreferenced tables --- bug, no?

At 10:34 AM 9/29/99 -0400, Tom Lane wrote:

>play=> select x.f1 from x, x as x2;
>f1
>--
> 1
> 2
> 3
>(3 rows)
>
>It seems to me that the latter query must yield 9 rows (three
>occurrences of each value) to satisfy the SQL spec.  The spec defines
>the result of a two-query FROM clause to be the Cartesian product of the
>two tables, period.  It doesn't say anything about "only if one or more
>columns of each table are actually used somewhere".

AFAIK, this is correct.  For the heck of it, I tried it in 
Oracle, and indeed the full cartesian product's returned:


SQL> select x2.i from x, x x2;
        I
----------        1        1        1        2        2        2        3        3        3

9 rows selected.

>play=> select count(1) from x;
>count
>-----
>    1
>(1 row)

Again, Oracle 8:

SQL> select count(1) from x, x x2;
 COUNT(1)
----------        9

SQL> 



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.