Thread: BUG #1784: "adding missing FROM-clause" when not needed

The following bug has been logged online:

Bug reference:      1784
Logged by:          Giacomo G
Email address:      matic999@hotmail.com
PostgreSQL version: 8.0.3
Operating system:   linux kernel 2.6.12.2
Description:        "adding missing FROM-clause" when not needed
Details:

If I populate the database with this two tables:

CREATE TABLE bar ( c varchar, d varchar);
CREATE TABLE foo ( a varchar, b varchar);
COPY bar (c, d) FROM stdin;
1       ghi
2       jkl
\.
COPY foo (a, b) FROM stdin;
1       abc
2       def
\.

When I run this select I get the output I expect:

test=# select * from foo t0 join bar t1 on ( t0.a = t1.c ) where t0.a = 1;
 a |  b  | c |  d
---+-----+---+-----
 1 | abc | 1 | ghi
(1 row)


But, when i run the same query with the real name of table in the where
statement I get this:

test=# select * from foo t0 join bar t1 on ( t0.a = t1.c ) where foo.a = 1;
NOTICE:  adding missing FROM-clause entry for table "foo"
 a |  b  | c |  d
---+-----+---+-----
 1 | abc | 1 | ghi
 2 | def | 2 | jkl
(2 rows)

while I expect the same result of the first query.

On Mon, Jul 25, 2005 at 03:03:54AM +0100, Giacomo G wrote:
> But, when i run the same query with the real name of table in the where
> statement I get this:
>
> test=# select * from foo t0 join bar t1 on ( t0.a = t1.c ) where foo.a = 1;
> NOTICE:  adding missing FROM-clause entry for table "foo"

See the SELECT documentation:

http://www.postgresql.org/docs/8.0/static/sql-select.html

"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."

If you disable add_missing_from then you'll get an error instead
of a notice and unexpected results.  Add_missing_from will be
disabled by default in PostgreSQL 8.1.

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

Giacomo G wrote:
>
> But, when i run the same query with the real name of table in the where
> statement I get this:

This isn't the "real name" of the table "t0". It is another reference to
table "foo", and as such should either raise an error or arrange to
alter the from-clause to make it valid (which is what happens).

> test=# select * from foo t0 join bar t1 on ( t0.a = t1.c ) where foo.a = 1;
> NOTICE:  adding missing FROM-clause entry for table "foo"
>  a |  b  | c |  d
> ---+-----+---+-----
>  1 | abc | 1 | ghi
>  2 | def | 2 | jkl
> (2 rows)
>
> while I expect the same result of the first query.

In recent versions, you can disable the feature in your postgresql.conf
by setting "add_missing_from" to false. See the manuals - run-time
environment / compatibility.

--
   Richard Huxton
   Archonet Ltd

"Giacomo G" <matic999@hotmail.com> writes:
> But, when i run the same query with the real name of table in the where
> statement I get this:

> test=# select * from foo t0 join bar t1 on ( t0.a = t1.c ) where foo.a = 1;
> NOTICE:  adding missing FROM-clause entry for table "foo"

That is correct --- the statement is not legal per SQL spec, and the
only valid way to interpret it is to treat "foo.a" as a separate
reference to the table.  See
http://www.postgresql.org/docs/8.0/static/queries-table-expressions.html#QUERIES-TABLE-ALIASES
or the SELECT reference page, which points out

alias

     A substitute name for the FROM item containing the alias. An alias
     is used for brevity or to eliminate ambiguity for self-joins (where
     the same table is scanned multiple times). 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.

            regards, tom lane