Thread: Join/table alias bug
Hi,
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. 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
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
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> ... 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.
Not only for historical reasons: there are cases where it allows you
to do things you couldn't easily do otherwise. An example is deleting
using a join:
DELETE FROM target WHERE field1 = source.field2
which deletes any record in target whose field1 matches any field2
value in source. This isn't SQL92 since DELETE doesn't allow you
to specify any tables except the target table in FROM. (Yeah,
I know this example could be written with a subselect --- but with
a more complex WHERE condition it gets harder to do that. Also
slower.)
> We've pondered many times about emitting warnings but a definite
> consensus was never reached.
Bruce had actually put in some code to emit warnings, but Thomas
objected to it for reasons I don't recall clearly. I think it was
an implementation issue rather than objecting to the idea of having
warnings. AFAIR we had pretty much agreed that a warning would be
a good idea.
IIRC, Bruce's code would emit a warning whenever an implicit RTE was
added. I think that might be overly verbose --- I'd be inclined to
warn only in the case that an implicit RTE is added for a table that
has an RTE already (under a different alias). That is the only
situation I've seen user complaints about.
regards, tom lane
Tom Lane writes: > Not only for historical reasons: there are cases where it allows you > to do things you couldn't easily do otherwise. An example is deleting > using a join: > > DELETE FROM target WHERE field1 = source.field2 Wow, that seems pretty bogus to me. > Bruce had actually put in some code to emit warnings, but Thomas > objected to it for reasons I don't recall clearly. I think it was along the lines of "it's not the backend's task to teach SQL". Incidentally, it could be, with the SQL flagger (sec. 4.34). -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Yes, this is what was eventually done... only emit warnings for tables already in the RTE, as Tom mentioned. > Peter Eisentraut <e99re41@DoCS.UU.SE> writes: > > ... 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. > > Not only for historical reasons: there are cases where it allows you > to do things you couldn't easily do otherwise. An example is deleting > using a join: > > DELETE FROM target WHERE field1 = source.field2 > > which deletes any record in target whose field1 matches any field2 > value in source. This isn't SQL92 since DELETE doesn't allow you > to specify any tables except the target table in FROM. (Yeah, > I know this example could be written with a subselect --- but with > a more complex WHERE condition it gets harder to do that. Also > slower.) > > > We've pondered many times about emitting warnings but a definite > > consensus was never reached. > > Bruce had actually put in some code to emit warnings, but Thomas > objected to it for reasons I don't recall clearly. I think it was > an implementation issue rather than objecting to the idea of having > warnings. AFAIR we had pretty much agreed that a warning would be > a good idea. > > IIRC, Bruce's code would emit a warning whenever an implicit RTE was > added. I think that might be overly verbose --- I'd be inclined to > warn only in the case that an implicit RTE is added for a table that > has an RTE already (under a different alias). That is the only > situation I've seen user complaints about. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026