Re: join error?

On Wed, Mar 30, 2005 at 02:46:37PM -0800, Kemin Zhou wrote:
> I just found out one strange behavior of the SQL engine.  This may be fixed.
>
> table 1
> create table table1 ( id integer, something text);
>
> create table table2 (id integer, otherthing text);
>
> select *
> from table1 t1, table2 t2
> where t1.id=table2.id;
>
> -- here I made a mistake in the where clause I should have used
> --  where t1.id=t2.id
>
> This query produces a lot of MORE rows than it should be, and it is
> doing cross join.

In 7.4 and later you'll get an error if you disable add_missing_from:

SET add_missing_from TO off;

SELECT *
FROM table1 t1, table2 t2
WHERE t1.id = table2.id;
ERROR:  missing FROM-clause entry for table "table2"

See the "Run-time Configuration" section of the "Server Run-time
Environment" chapter in the documentation for more info.

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#GUC-ADD-MISSING-FROM

There's been a proposal to disable add_missing_from by default in
a future version of PostgreSQL.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/