Re: [HACKERS] union all bug?
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] 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 Thanks for the explanation. Now it makes sense :-) Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] union all bug?
Joe Conway [EMAIL PROTECTED] 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. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] union all bug?
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 [EMAIL PROTECTED] wrote: Joe Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] union all bug?
Gurjeet Singh [EMAIL PROTECTED] writes: 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... Yeah. In theory that's unambiguous, but IIRC we couldn't persuade bison of the fact, so you have to add parens. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
