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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.