Re: query with table alias

On Nov 12, 2004, at 7:48 PM, Rodríguez Rodríguez, Pere wrote:

> prr=# select foo.c1, f.c2 from foo f;  -- >>> Incorrect result <<<
>  c1 | c2
> ----+----
>   1 |  1
>   2 |  1
>   1 |  2
>   2 |  2
> (4 filas)

If you alias a table, you can only reference the table using the alias.
It is in effect renamed: foo is no longer foo. It is only f after you
alias foo to f.

> The result of the "select foo.c1, f.c2 from foo f" isn't correct, it
> do a cartesian product of foo table.

When you do reference foo, PostgreSQL adds foo to the FROM list if you
have the ADD_MISSING_FROM configuration parameter set to TRUE in
postgresql.conf.

Thus, PostgreSQL considers your query to be
SELECT foo.c1, f.c2 FROM foo f, foo;

which results in the Cartesian join your are seeing.

Some people like this, some people don't. (I'm one of the latter.) If
you would rather PostgreSQL throws an error in this situation, set
ADD_MISSING_FROM to FALSE.

Hope this helps.

Michael Glaesemann
grzm myrealbox com