Thread: Table alias fun == incorrectly adding tables to join...
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
--
Sean Chittenden
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.
> NOTICE: adding missing FROM-clause entry for table "table_s1" > NOTICE: adding missing FROM-clause entry for table "table_s2" > 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. This is a PostgreSQL extension to make it easier on those using the database. In fact, it's the only way to currently write a delete using a join. In 7.4 you can disable this extension in postgresql.conf (add_missing_from =3D false)
> > 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.
I'm not 100% sure what the definition of invalid is... If I turn off
add_missing_from, it still fails. ex:
test=# EXPLAIN ANALYZE SELECT table_s1.i, table_s2.j FROM table_s1 AS s1, table_s2 AS s2 WHERE s1.i = s2.j;
ERROR: missing FROM-clause entry for table "table_s1"
I know the docs say, "When an alias is provided, it completely hides
the actual name of the table or function; for example given FROM foo
AS f, the remainder of the SELECT must refer to this FROM item as f
not foo." It just struck me as a strange behavior.
> 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.
Correct. This is another IRC special, so I don't really care one way
or another, but it was certainly aggravating to track it down so I
figured I'd report it as the behavior seems a tad bogus in some cases,
though I do appreciate the value of being able to join a table on
itself.... it just seems as though users stumble across this more
often than they join a table with itself. -sc
--
Sean Chittenden
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.
>
> I'm not 100% sure what the definition of invalid is... If I turn off
> add_missing_from, it still fails. ex:
>
> test=# EXPLAIN ANALYZE SELECT table_s1.i, table_s2.j FROM table_s1 AS s1, table_s2 AS s2 WHERE s1.i = s2.j;
> ERROR: missing FROM-clause entry for table "table_s1"
>
> I know the docs say, "When an alias is provided, it completely hides
> the actual name of the table or function; for example given FROM foo
> AS f, the remainder of the SELECT must refer to this FROM item as f
> not foo." It just struck me as a strange behavior.
That's AFAIK the required behavior for SQL (that a table alias hides the
other name for the table) and thus for SQL the query would be malformed
and violate some grammar or syntax rule when it tries to alias a name that
doesn't exist (because table_s1.i or table_s2.j don't resolve to something
out of the FROM list).