From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Joe Conway" <mail(at)joeconway(dot)com>, "Hackers (PostgreSQL)" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: union all bug? |
Date: | 2006-06-18 16:43:13 |
Message-ID: | 65937bea0606180943r1a3dd332i81f524c05bf0d86@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Probably this explains the ERROR for the last query... The ORDER BY
and LIMIT clauses are expected to end a query (except for subqueries,
of course), and hence the keyword UNION is not expected after the
LIMIT clause...
On 6/18/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
> > I was trying to work around limitations with "partitioning" of tables
> > using constraint exclusion, when I ran across this little oddity:
>
> I think you're under a misimpression about the syntax behavior of ORDER
> BY and UNION. Per spec, ORDER BY binds less tightly than UNION, thus
>
> select foo union select bar order by x
>
> means
>
> (select foo union select bar) order by x
>
> If you want to apply ORDER BY to either arm of a union individually,
> you need parentheses, eg
>
> (select foo order by x) union (select bar order by x)
>
> (Note that this construct fails to guarantee that the output of the
> union will be sorted by x!) LIMIT is not in the spec but we treat
> it like ORDER BY for this purpose.
To guarantee the ordering, one can use
select * from (select foo from A union select bar from B) order by x
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-06-18 16:58:09 | Re: union all bug? |
Previous Message | Tom Lane | 2006-06-18 16:31:01 | Re: union all bug? |