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

aliases break my query

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: aliases break my query
Date: 2000-05-26 03:11:44
Message-ID: 392DEB70.86A115E4@selectacast.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
These two queries are exactly alike. The first one uses aliases except
for the order by. The second uses aliases also for the order by. The
third uses whole names.  The third has the behavior I want.

Someone please tell me what I am doing wrong. I don't want to have to
use whole names for my query.

The data for the tables are at the end.


playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
ta.a) from tablea ta, tableb tb order by tablea.a;
a|b|c|?column?
-+-+-+--------
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       1
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       1
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       1
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       1
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       1
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       1
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       1
3|4|5|       0
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       1
4|5|4|       0
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       1
1|2| |       0
2|3|4|       0
3|4|5|       0
4|5|4|       0
(80 rows)

playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
ta.a) from tablea ta, tableb tb order by ta.a;
a|b|c|?column?
-+-+-+--------
1|2| |       0
1|2| |       0
1|2| |       0
1|2| |       0
1|2| |       0
2|3|4|       1
2|3|4|       1
2|3|4|       0
2|3|4|       0
2|3|4|       0
3|4|5|       0
3|4|5|       0
3|4|5|       1
3|4|5|       0
3|4|5|       0
4|5|4|       0
4|5|4|       0
4|5|4|       0
4|5|4|       1
4|5|4|       0
(20 rows)

playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
where tableb.yy = tablea.a) order by tablea.a;
a|b|c|?column?
-+-+-+--------
1|2| |       0
2|3|4|       2
3|4|5|       1
4|5|4|       1
(4 rows)

playpen=> 
playpen=> select * from tablea;
a|b|c
-+-+-
1|2| 
2|3|4
3|4|5
4|5|4
(4 rows)

playpen=> select * from tableb;
yy|zz
--+--
 2| 4
 2| 5
 3| 9
 4|14
 5|15
(5 rows)

Responses

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2000-05-26 03:16:06
Subject: RE: Orphaned locks in 7.0?
Previous:From: Bruce MomjianDate: 2000-05-26 02:15:41
Subject: Re: Re: gram.y PROBLEM with UNDER

pgsql-sql by date

Next:From: Tom LaneDate: 2000-05-26 04:35:14
Subject: Re: aliases break my query
Previous:From: Tom LaneDate: 2000-05-26 00:03:18
Subject: Re: Use of index in 7.0 vs 6.5

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