union all bug?
I was trying to work around limitations with "partitioning" of tables
using constraint exclusion, when I ran across this little oddity:
-- works
test=# select * from (select time from url_access_2006_06_07 order by 1
limit 2) as ss1;
time
---------------------
2006-06-07 15:07:41
2006-06-07 15:07:41
(2 rows)
-- works
test=# select time from url_access_2006_06_08 order by 1 limit 2;
time
---------------------
2006-06-08 15:07:41
2006-06-08 15:07:41
(2 rows)
-- huh ?!?
test=# select * from (select time from url_access_2006_06_07 order by 1
limit 2) as ss1 union all select time from url_access_2006_06_08 order
by 1 limit 2;
time
---------------------
2006-06-07 15:07:41
2006-06-07 15:07:41
(2 rows)
-- works
test=# select * from (select time from url_access_2006_06_07 order by 1
limit 2) as ss1 union all select * from (select time from
url_access_2006_06_08 order by 1 limit 2) as ss2;
time
---------------------
2006-06-07 15:07:41
2006-06-07 15:07:41
2006-06-08 15:07:41
2006-06-08 15:07:41
(4 rows)
I get an error if I try to eliminate the first FROM clause subselect:
test=# select time from url_access_2006_06_07 order by 1 limit 2 union
all select * from (select time from url_access_2006_06_08 order by 1
limit 2) as ss2;
ERROR: syntax error at or near "all" at character 65
LINE 1: ...om url_access_2006_06_07 order by 1 limit 2 union all select...
So I'm wondering whether the second FROM clause subselect is really
required, but not getting enforced as it should?
Joe
Joe Conway <mail@joeconway.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.
regards, tom lane
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@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.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, thusselect 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
"Gurjeet Singh" <singh.gurjeet@gmail.com> 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
Tom Lane wrote:
Joe Conway <mail@joeconway.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
Thanks for the explanation. Now it makes sense :-)
Joe