Обсуждение: 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, 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

"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