Re: Join/table alias bug - Mailing list pgsql-bugs
From | Peter Eisentraut |
---|---|
Subject | Re: Join/table alias bug |
Date | |
Msg-id | Pine.GSO.4.02A.10004201253470.11258-100000@Gris.DoCS.UU.SE Whole thread Raw |
In response to | Join/table alias bug (Adriaan Joubert <a.joubert@albourne.com>) |
Responses |
Re: [HACKERS] Re: Join/table alias bug
|
List | pgsql-bugs |
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
pgsql-bugs by date: