Re: Join/table alias bug

On Thu, 20 Apr 2000, Adriaan Joubert wrote:

> I could not understand why I was getting 6 rows back, when I should only
> have been getting one back, until I realised that I had given an alias
> for the table 'fund_class' without using it in the first case.

This is a common problem. According to the standard, queries like

SELECT my_tbl.a FROM my_tbl alias

are invalid because the table "my_tbl" is named "alias" for the purpose of
the select clause, so "my_tbl" doesn't refer to anything. It's an
extension on the part of PostgreSQL to infer that my_tbl probably refers
to a table named "my_tbl", but then you are talking about the same as

SELECT my_tbl.a FROM my_tbl alias, my_tbl

(second entry in from list implicitly added), for which the behaviour you
saw is correct. The reason this behaves that way is because queries
without from lists (SELECT my_tbl.a) are valid in PostgreSQL for
historical reasons, so we're stuck with it. We've pondered many times
about emitting warnings but a definite consensus was never reached.

If I use
> the alias I get the expected result. Perhaps this should raise an error,
> but I think the two queries should not give a different results. This is
> with postgres 7.0beta5 on Dec-Alpha.
>
> select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id
> from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip,
> fund_class f
> where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and
> it.el_id=fund_class.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id;
>
> fc_id | el_id | ip_id | c_id | ip_id
> -------+-------+-------+------+-------
> 2 | 6 | 6 | 9 | 6
> 3 | 6 | 6 | 9 | 6
> 5 | 6 | 6 | 9 | 6
> 4 | 6 | 6 | 9 | 6
> 7 | 6 | 6 | 9 | 6
> 6 | 6 | 6 | 9 | 6
> (6 rows)
>
>
> select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id
> from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip,
> fund_class f
> where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and
> it.el_id=f.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id;
>
> fc_id | el_id | ip_id | c_id | ip_id
> -------+-------+-------+------+-------
> 6 | 6 | 6 | 9 | 6
> (1 row)
>
> Adriaan
>
>

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden