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@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden