Yes! Your suggestion makes perfect sense. I tested and fixed the subselect first, but gave up on the order by until you helped out.
My problem was a lack of knowledge of if this was a bug or not, since the query had been working for well over a year without problems.
The current working query is now:
(select o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id) as price_to_display, o.user_id, u.first_names, u.last_name, count(*) as n_items
from ec_orders o, users u, ec_items i
where o.user_id=u.user_id
and o.order_id=i.order_id
and trunc((sysdate() - o.confirmed_date)) <= 1 and o.order_state='confirmed'
group by o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id), o.user_id, u.first_names, u.last_name)
union
(select o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id) as price_to_display, o.user_id, ''::char as first_names, ''::char as last_name, count(*) as n_items
from ec_orders o, ec_items i
where 0=(select count(*) from users where user_id = ec_orders.user_id)
and o.order_id=i.order_id
and trunc((sysdate() - o.confirmed_date)) <= 1 and o.order_state='confirmed'
group by o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id), o.user_id, first_names, last_name)
order by order_id
The new correct way does make sense.
