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

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, Pennsylvania 19026