Behavior of subselects in target lists and order by

From: amit sehas <cun23(at)yahoo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: pgsql-general(at)postgresql(dot)org
Subject: Behavior of subselects in target lists and order by
Date: 2012-02-24 20:45:41
Message-ID: 1330116341.57093.YahooMailClassic@web160503.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

If we have a query of the form:

Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER BY 1 WHERE p3 = 75

In the above query there is a subselect in the target list and the ORDERBY has an ordinal number which indicates order by column 1. Does this mean that the above query will return all results from T1 that match p3 =75 and all results from T2 that match p2 = T1.p1 for every match on T1 and order them all by the first column of T1 and T2 ?

basically i am trying to determine if the order by clause has effect only on the tuples of the outer select or both the outer and inner select. Or the results returned by the inner select are treated as if they are part of a single tuple which includes the tuple from table T1 ?

Is this an implementation specific behaviour or it conforms to the SQL standard ...?

thanks
-Amit

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2012-02-24 20:50:16 Re: Upgrade to 9.1 causing function problem
Previous Message Adrian Klaver 2012-02-24 20:40:07 Re: Re: When I try to connect to a database, I get the following error : psql teleflowdb8

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2012-02-24 20:55:33 COPY with hints, rebirth
Previous Message Christopher Browne 2012-02-24 20:01:01 Re: Triggers with DO functionality