Re: query with table alias - Mailing list pgsql-general

From Patrick Fiche
Subject Re: query with table alias
Date
Msg-id 016f01c4c8a8$a136a370$2e01a8c0@pc3648pfi
Whole thread Raw
In response to query with table alias  ("Rodríguez Rodríguez, Pere" <prr@hosppal.es>)
List pgsql-general
The correct query would be "select f.c1, f.c2 from foo f;" if you want to use alias....
 
Patrick
 

    -------------------------------------------------------------------------------------------
    Patrick Fiche
    email : patrick.fiche@aqsacom.com
    tél : 01 69 29 36 18
    -------------------------------------------------------------------------------------------

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Rodríguez Rodríguez, Pere
Sent: vendredi 12 novembre 2004 11:49
To: 'pgsql-general@postgresql.org'
Subject: [GENERAL] query with table alias

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,





Protected by Polesoft Lockspam

http://www.polesoft.com/refer.html

pgsql-general by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: query with table alias
Next
From: Matteo Beccati
Date:
Subject: Re: query with table alias