Re: Bug or stupidity

On Sat, Oct 23, 2004 at 02:17:16PM +0000, Philip Hofstetter wrote:
> Hello,
>
> I think, I found a bug, but maybe it's just my stupidity. Granted: What
> I did was an error on my part, but I still think, PostgreSQL should not
> do what it does.

... snip ...

> popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
> b aliasb using (id) order by b.name2;
> NOTICE: adding missing FROM-clause entry for table "b"
> name | name2
> -------+-----------
> gnegg | gnegglink
> blepp | blepplink
> gnegg | gnegglink
> blepp | blepplink
> (4 rows)

See that NOTIVCE? It's telling you that it's converted your query to:

select aliasa.name, aliasb.name2 from b, a aliasa left join
b aliasb using (id) order by b.name2;

Since you now have an unconstrained join on the B table, you get twice
as many rows.

It basically comes down to, if you make an alias, you have to use the
alias. You can't use the original table name *and* the alias. The
reference to the original table is becomes another copy of the same
table.

As for what's SQL standard, I think by a strict definition your query
shouldn't be allowed at all, referencing an undefined table.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.