Thread: join error?

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.

I am not sure this is considered a normal behavior or not. Whatever, the sql programmers
should be aware such mistakes.

If you have any comments please let me know.

Thanks.

Kemin

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/