From: | "Rodríguez Rodríguez, Pere" <prr(at)hosppal(dot)es> |
---|---|
To: | "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | query with table alias |
Date: | 2004-11-12 10:48:44 |
Message-ID: | B35596C2EFF3D2118EE100A0C94B2A60014BFB53@palamos_nt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I think I have found a query problem when the query has an alias for a table
and use alias item and table name.
I ilustrate the problem with a simple table and query.
prr=# create table foo (c1 int2, c2 int2);
CREATE TABLE
prr=# insert into foo values (1, 1);
INSERT 301891 1
prr=# insert into foo values (2, 2);
INSERT 301892 1
prr=# select * from foo;
c1 | c2
----+----
1 | 1
2 | 2
(2 filas)
prr=# select foo.c1, f.c2 from foo f; -- >>> Incorrect result <<<
c1 | c2
----+----
1 | 1
2 | 1
1 | 2
2 | 2
(4 filas)
prr=# explain select foo.c1, f.c2 from foo f; -- >>> Incorrect query plan
<<<
QUERY PLAN
-------------------------------------------------------------------
Nested Loop (cost=20.00..20040.00 rows=1000000 width=4)
-> Seq Scan on foo f (cost=0.00..20.00 rows=1000 width=2)
-> Materialize (cost=20.00..30.00 rows=1000 width=2)
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=2)
(4 filas)
The result of the "select foo.c1, f.c2 from foo f" isn't correct, it do a
cartesian product of foo table.
My version of PostgreSQL is PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled
by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9).
Is this a bug?, is fixed in posteriors versions?
Thanks in advance,
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2004-11-12 11:09:04 | Re: query with table alias |
Previous Message | Oliver Elphick | 2004-11-12 08:52:07 | Re: making another super user other than postgres |