Re: Table alias fun == incorrectly adding tables to join...

On Mon, 22 Sep 2003, Sean Chittenden wrote:

> Here's a fun one.
>
> test=# CREATE TABLE table_s1 (i int);
> test=# CREATE TABLE table_s2 (j int);
> test=# EXPLAIN ANALYZE SELECT table_s1.i, table_s2.j FROM table_s1 AS s1, table_s2 AS s2 WHERE s1.i = s2.j;
> NOTICE: adding missing FROM-clause entry for table "table_s1"
> NOTICE: adding missing FROM-clause entry for table "table_s2"
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..0.03 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=1)
> -> Nested Loop (cost=0.00..0.02 rows=1 width=4) (actual time=0.02..0.02 rows=0 loops=1)
> -> Nested Loop (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=1)
> Join Filter: ("outer".i = "inner".j)
> -> Seq Scan on table_s1 s1 (cost=0.00..0.00 rows=1 width=4) (actual time=0.01..0.01 rows=0 loops=1)
> -> Seq Scan on table_s2 s2 (cost=0.00..0.00 rows=1 width=4) (never executed)
> -> Seq Scan on table_s1 (cost=0.00..0.00 rows=1 width=4) (never executed)
> -> Seq Scan on table_s2 (cost=0.00..0.00 rows=1 width=4) (never executed)
> Total runtime: 0.20 msec
> (9 rows)
>
> If there's real data in the tables, this joins the tables on itself
> and execution times explode, naturally. I don't know if the spec says
> this is the correct behavior or not, but I have a feeling its not and
> this is a bug. -sc

Well, the query is technically invalid I think.

The above is the expected behavior for 7.3 and earlier I believe (note the
notices). IIRC, 7.4 has a guc option to turn the adding of missing from
clauses off.