query with table alias

[Rodríguez Rodríguez, Pere]

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..20 040.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,

[Michael Glaesemann]

On Nov 12, 2004, at 7:48 PM, Rodríguez Rodríguez, Pere wrote:

prr=# select foo.c1, f.c2 from foo f;* -- >>> Incorrect result <<<
*c1 | c2
----+----
* 1 |* 1
* 2 |* 1
* 1 |* 2
* 2 |* 2
(4 filas)
If you alias a table, you can only reference the table using the alias.
It is in effect renamed: foo is no longer foo. It is only f after you
alias foo to f.
The result of the "select foo.c1, f.c2 from foo f" isn't correct, it
do a cartesian product of foo table.

When you do reference foo, PostgreSQL adds foo to the FROM list if you
have the ADD_MISSING_FRO M configuration parameter set to TRUE in
postgresql.conf .

Thus, PostgreSQL considers your query to be
SELECT foo.c1, f.c2 FROM foo f, foo;

which results in the Cartesian join your are seeing.

Some people like this, some people don't. (I'm one of the latter.) If
you would rather PostgreSQL throws an error in this situation, set
ADD_MISSING_FRO M to FALSE.

Hope this helps.

Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Patrick Fiche]

The correct query would be "select f.c1, f.c2 from foo f;" if you want to
use alias....

Patrick
---------------------------------------------------------------------------
----------------
Patrick Fiche
email : pa***********@a qsacom.com
tél : [phone]
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Rodríguez Rodríguez,
Pere
Sent: vendredi 12 novembre 2004 11:49
To: 'p************@ 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..20 040.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

[Matteo Beccati]

prr=# select foo.c1, f.c2 from foo f; -- >>> Incorrect result <<<

The result of the "select foo.c1, f.c2 from foo f" isn't correct, it do
a cartesian product of foo table.

foo is aliased to f, so there's no table named foo in the from clause.
By default postgres tries to add the missing table name, so your query
get rewritten as:

select foo.c1, f.c2 from foo f, foo;

You should also receive a:

NOTICE: adding missing FROM-clause entry for table "foo"
Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html