Обсуждение: Problems with UNION ALL and ORDER BY

Dear friends...,I have the following problem:


select
.....
from
....
where
....UNION ALL

select
...
from
....
where
....
ORDER BY field1

But the the order by doesn't work properly. It returns the rows of the 
first query ordered and then appends the rows of the second query 
ordered. But this is not what i expect. I expect the result of both 
queries to be orderd. So I try.

SELECT TEMP.*
FROM
(
select
.....
from
....
where
....UNION ALL

select
...
from
....
where
....
) TEMP
ORDER BY TEMP.field1

But this also doesn't work. Any ideas.p

Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
>  I have the following problem:

> select
> .....
> from
> ....
> where
> ....
>  UNION ALL

> select
> ...
> from
> ....
> where
> ....
> ORDER BY field1

> But the the order by doesn't work properly. It returns the rows of the 
> first query ordered and then appends the rows of the second query 
> ordered.

Pray tell, what Postgres release are you using?

AFAICT this will result in an overall sort in all PG releases since 7.0.
I don't have anything older to test...
        regards, tom lane

Dear Tom,

As I say in my previous letter I am using 7.2.3. If you wish I can show 
you the query and the result to see for yourself, that there is 
something wrong. It just don't order the overall result but the separate 
results of the both subqueries.

Tom Lane wrote:

>Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
>  
>
>> I have the following problem:
>>    
>>
>
>  
>
>>select
>>.....
>>from
>>....
>>where
>>....
>> UNION ALL
>>    
>>
>
>  
>
>>select
>>...
>>from
>>....
>>where
>>....
>>ORDER BY field1
>>    
>>
>
>  
>
>>But the the order by doesn't work properly. It returns the rows of the 
>>first query ordered and then appends the rows of the second query 
>>ordered.
>>    
>>
>
>Pray tell, what Postgres release are you using?
>
>AFAICT this will result in an overall sort in all PG releases since 7.0.
>I don't have anything older to test...
>
>            regards, tom lane
>
>
>  
>

Kaloyan Iliev Iliev wrote:
> 
> Dear friends...,
> I have the following problem:
> 
> 
> select  .....  from  ....  where  ....
> UNION ALL
> 
> select ...  from  ....  where  ....
> ORDER BY field1
> 
> But the the order by doesn't work properly. It returns the rows of the 
> first query ordered and then appends the rows of the second query 
> ordered. But this is not what i expect. I expect the result of both 
> queries to be orderd. So I try.
> 
> SELECT TEMP.*
> FROM
> (
> select ..... from  ....  where  ....
> UNION ALL
> 
> select ...  from  ....  where ....
> ) TEMP
> ORDER BY TEMP.field1
> 
> But this also doesn't work. Any ideas.p

This have to work. Could you please provide a real example ( creation table,
insertion data and query execution ).


Regards
Gaetano Mendola

Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
> As I say in my previous letter I am using 7.2.3. If you wish I can show 
> you the query and the result to see for yourself, that there is 
> something wrong. It just don't order the overall result but the separate 
> results of the both subqueries.

Quite honestly, I don't believe it.  In 7.2 I get

regression=# explain select * from tenk1 a union all select * from tenk1 b order by unique1;
NOTICE:  QUERY PLAN:

Sort  (cost=3128.28..3128.28 rows=20000 width=148) ->  Append  (cost=0.00..760.00 rows=20000 width=148)       ->
SubqueryScan *SELECT* 1  (cost=0.00..380.00 rows=10000 width=148)             ->  Seq Scan on tenk1 a
(cost=0.00..380.00rows=10000 width=148)       ->  Subquery Scan *SELECT* 2  (cost=0.00..380.00 rows=10000 width=148)
        ->  Seq Scan on tenk1 b  (cost=0.00..380.00 rows=10000 width=148)
 

EXPLAIN

and as you can see there's only one sort step being applied to the union
result.

Now if EXPLAIN shows you a different sorting structure for your query,
then I'd be interested to see the exact query and the EXPLAIN output.
But what I think is that you are misinterpreting the sorting result you
get.  If you are using a non-C locale you may be seeing some pretty
weird sorting rules :-(
        regards, tom lane