CODEWITHSUNDEEP
databaseoracle-databasesortingdate
Adam Brokes
Adam Brokes

Reputation: 161

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

When I run this query, results aren't sorted correctly.

SELECT 
  SYSDATE - datecolumn a,
  SYSDATE - datecolumn sortcolumn
FROM atable
ORDER BY sortcolumn

Produces:

2576.780243055555555555555555555555555556
2586.297013888888888888888888888888888889
2342.294479166666666666666666666666666667
2617.297476851851851851851851851851851852
2624.855104166666666666666666666666666667
2624.855138888888888888888888888888888889
2624.854236111111111111111111111111111111
2372.296643518518518518518518518518518519
2645.257800925925925925925925925925925926
2403.294756944444444444444444444444444444
2676.297696759259259259259259259259259259

When the first line (SYSDATE - datecolumn a,) is removed, everything works fine. Is there any particular reason why this is happening?

Upvotes: 9

Views: 469

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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 b
from members
order by b;

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191265

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.

Upvotes: 5

Related Questions