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.
=?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= <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.
Show quoted text
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?