Skip site navigation (1) Skip section navigation (2)

Join/table alias bug

From: Adriaan Joubert <a(dot)joubert(at)albourne(dot)com>
To: pgsql-hackers(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org
Subject: Join/table alias bug
Date: 2000-04-20 09:12:25
Message-ID: 38FEC9F9.C4E342D@albourne.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Hi,

	I could not understand why I was getting 6 rows back, when I should
only
have been getting one back, until I realised that I had given an alias
for the table 'fund_class' without using it in the first case. If I use
the alias I get the expected result. Perhaps this should raise an error,
but I think the two queries should not give a different results. This is
with postgres 7.0beta5 on Dec-Alpha.

	select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id 
	from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip, 	 
 		fund_class f 
	where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and
		it.el_id=fund_class.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id;

 fc_id | el_id | ip_id | c_id | ip_id 
-------+-------+-------+------+-------
     2 |     6 |     6 |    9 |     6
     3 |     6 |     6 |    9 |     6
     5 |     6 |     6 |    9 |     6
     4 |     6 |     6 |    9 |     6
     7 |     6 |     6 |    9 |     6
     6 |     6 |     6 |    9 |     6
(6 rows)


	select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id 
	from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip,
		fund_class f 
	where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and
		it.el_id=f.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id;

 fc_id | el_id | ip_id | c_id | ip_id 
-------+-------+-------+------+-------
     6 |     6 |     6 |    9 |     6
(1 row)

Adriaan

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2000-04-20 10:59:53
Subject: Re: Join/table alias bug
Previous:From: Ryan KirkpatrickDate: 2000-04-17 02:48:46
Subject: Re: [HACKERS] Linux/Alpha and Postgres 7.0 Beta Status

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2000-04-20 10:59:53
Subject: Re: Join/table alias bug
Previous:From: Tom LaneDate: 2000-04-20 03:05:48
Subject: Re: Bug in psql COPY command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group