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.
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, you can add a new column containing only 1s in the first table and only 2s in the second table, then order by that (ORDER BY 1 orders by the first column):
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