Oracle doesn't sort properly when using the same column twice

This appears to be a bug. This behaviour looks similar to bug 8675087, which is supposedly fixed in 11.2.0.2 - and indeed I can't reproduce the test case for that. But I still see your behaviour in that version (on SQL Fiddle) and in 11.2.0.3, so this is similar but not exactly the same...

If this is causing you a real issue and you can't find a workaround you're happy with (like swapping the column order; if your sortcolumn is the first expression that uses sysdate it seems to be OK, but swapping columns in your real query might not be convenient) then you should raise a service request with Oracle.

Fascinating bug. Reproduced on 11.2.0.2.

Even happens if the query is wrapped in a subquery:

select * from (  select sysdate - db_created_on a        ,sysdate - db_created_on b  from members) order by b;

(results not sorted correctly)

select * from (  select sysdate - db_created_on a        ,sysdate - db_created_on b  from members) order by a;

(results sorted correctly)

The best workaround I can think of is to use a scalar subquery for SYSDATE, this seems to cause it to sort correctly:

select sysdate - db_created_on a      ,(select sysdate from dual) - db_created_on bfrom membersorder by b;