FAQ
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

Search Discussions

  • Tom Lane at Jun 18, 2006 at 4:31 pm

    Joe Conway 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
  • Gurjeet Singh at Jun 18, 2006 at 4:43 pm
    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 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

    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
  • Tom Lane at Jun 18, 2006 at 4:58 pm

    "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
  • Joe Conway at Jun 20, 2006 at 9:55 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 18, '06 at 3:36p
activeJun 20, '06 at 9:55p
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase