Thread: Bug in 7.1.3 and 7.2?
there seems to be a problem in 7.1.3 and 7.2: test=# select * from a;id ---- 1 2 3 (3 rows) test=# select * from b;id ---- 3 4 (2 rows) test=# select a.*, d.* from a as c, b as d where a.id=b.id; NOTICE: Adding missing FROM-clause entry for table "a" NOTICE: Adding missing FROM-clause entry for table "b"id | id ----+---- 3 | 3 3 | 3 3 | 3 3 | 4 3 | 4 3 | 4 (6 rows) Althoug c and d are aliases for a and b, the two tables are added to the list in the from clause. Is this the desired behaviour or is it a bug? Hans
> test=# select a.*, d.* from a as c, b as d where a.id=b.id; > NOTICE: Adding missing FROM-clause entry for table "a" > NOTICE: Adding missing FROM-clause entry for table "b" > id | id > ----+---- > 3 | 3 > 3 | 3 > 3 | 3 > 3 | 4 > 3 | 4 > 3 | 4 > (6 rows) > > Althoug c and d are aliases for a and b, the two tables are added to the > list in the from clause. Is this the desired behaviour or is it a bug? AFAIK this is the desired behavior. Once you've aliased the tables you can't go back, there is no "a" or "b" any longer, just "c" and "d" from those entries.
Hans-Jürgen Schönig <hs@cybertec.at> writes: > test=# select a.*, d.* from a as c, b as d where a.id=b.id; > NOTICE: Adding missing FROM-clause entry for table "a" > NOTICE: Adding missing FROM-clause entry for table "b" > Althoug c and d are aliases for a and b, the two tables are added to the > list in the from clause. Is this the desired behaviour or is it a bug? This is not a bug. If the system treated different aliases for a table as interchangeable, then there'd be no possibility of doing a self-join. "a.*" is not a legal reference to a FROM entry "a as c": the AS alias *totally* hides the underlying table name as far as this query is concerned. What you have above is interpreted as from a as c, b as d, a as a, b as b The NOTICEs are intended to warn you that this is going on. If we took a hardline approach to enforcing the letter of the SQL92 standard, we'd reject this query as illegal: SQL92 doesn't allow implicit FROM entries. (And no, it doesn't think "a.*" refers to "a as something-else", either.) regards, tom lane
Ooh, ooh, pick me. I know what happened!!!!!!! The actual table for a was selected. The alias for be was selected (d). The where clause involved a and b, not a and d. Notice that it added the missing from clauses? Well, since the where clause did not apply (it applied only to a joining to b, but you selected a and d) it simply joined every row in a to every row in d, just like it should have. You can use this to create an alias to a table and then join it to itself. If this behavoir was not like this, it would not allow this added functionality. Make sure that your tables selected from and your where clause match. > test=# select a.*, d.* from a as c, b as d where a.id=b.id; > NOTICE: Adding missing FROM-clause entry for table "a" > NOTICE: Adding missing FROM-clause entry for table "b" > id | id > ----+---- > 3 | 3 > 3 | 3 > 3 | 3 > 3 | 4 > 3 | 4 > 3 | 4 > (6 rows) > > Althoug c and d are aliases for a and b, the two tables are added to the > list in the from clause. Is this the desired behaviour or is it a bug?