Q:

union all in SQL (Postgres) mess the order

I have a query which is order by date , there is  the query I have simplified it a bit but basically is :
select * from 
(select  start_date, to_char(end_date,'YYYY-mm-dd') as end_date from date_table
order by start_date ,end_date ) 
where start_date is null or end_date is null

It shows prefect order
but I add
union all
select start_date, 'single missing day' as end_date  from 
calendar_dates
where db_date>'2017-12-12' and db_date<'2018-05-13'     

Then the whole order messed up. Why is that happened? Union or union all should just append the dataset from first query with the second, right? It should not mess the order in the first query, right? 
I know this query doesn't makes any sense, but I have simplified it to
show the syntax.

A:

You can't predict what would be the order outcome by just assuming that UNION ALL will append queries in the order you write them.
The query planner will execute your queries in whatever order it sees it fit. That's why you have the ORDER BY clause. Use it !
For example, if you want to force the order of the first query, then the second, do :
select * from 
(select  1, start_date, to_char(end_date,'YYYY-mm-dd') as end_date from date_table
order by start_date ,end_date ) 
where start_date is null or end_date is null
union all
select 2, start_date, 'single missing day' as end_date  from 
calendar_dates
where db_date>'2017-12-12' and db_date<'2018-05-13' 
ORDER BY 1

