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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--=-5Ax6rmWn5p6rMJxh/nLL
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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)

--=-5Ax6rmWn5p6rMJxh/nLL
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/b1fF6DETLow6vwwRAvjYAJ4nixoyA6OJHFupJIPgkwnKNqAK7w CaAnuC
bbLRA4kZ2umBCMDkUZjbEGM=
=c+Dh
-----END PGP SIGNATURE-----

--=-5Ax6rmWn5p6rMJxh/nLL--

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.

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)