Oracle UNPIVOT and SYSDATE giving weird results

Question

I am trying to transpose columns to rows using query similar to the following...

WITH 
query AS
(
    SELECT    SYSDATE AS SomeDate,
              'One' AS One,
              'Two' AS Two, 
              'Three' AS Three,
              'Four' AS Four,
              'Five' AS Five
        FROM dual
),
up_query AS
(
    SELECT * 
    FROM query
    UNPIVOT 
    ( 
     NUM FOR DUMMY 
     IN 
     ( 
      One AS 'One',
      Two AS 'Two',
      Three AS 'Three',
      Four AS 'Four',
      Five AS 'Five'
     )
    )
)
SELECT SYSDATE, b.*
  FROM up_query  b;

I was expecting SomeDate to reflect SYSDATE for the resulting rows... But this is the result I am getting:

SYSDATE   SOMEDATE       DUMMY  NUM
09-DEC-11 09-DEC-07      One    One
09-DEC-11 09-DEC-07      Two    Two
09-DEC-11 09-DEC-07      Three  Three
09-DEC-11 09-DEC-07      Four   Four
09-DEC-11 09-DEC-07      Five   Five

Why is the SOMEDATE 4 years earlier than SYSDATE?

Answer 1

This appears to be a bug in 11.2.0.2. I can reproduce your results on Linux x86-64, 11.2.0.2.

But, on 11.2.0.3, on Linux x86-64, I get:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 10 01:20:32 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> WITH
  2  query AS
  3  (
  4      SELECT    SYSDATE AS SomeDate,
  5                'One' AS One,
  6                'Two' AS Two,
  7                'Three' AS Three,
  8                'Four' AS Four,
  9                'Five' AS Five
 10          FROM dual
 11  ),
 12  up_query AS
 13  (
 14      SELECT *
 15      FROM query
 16      UNPIVOT
 17      (
 18       NUM FOR DUMMY
 19       IN
 20       (
 21        One AS 'One',
 22        Two AS 'Two',
 23        Three AS 'Three',
 24        Four AS 'Four',
 25        Five AS 'Five'
 26       )
 27      )
)
 28   29  SELECT SYSDATE, b.*
 30    FROM up_query  b;

SYSDATE   SOMEDATE  DUMMY NUM
--------- --------- ----- -----
10-DEC-11 10-DEC-11 One   One
10-DEC-11 10-DEC-11 Two   Two
10-DEC-11 10-DEC-11 Three Three
10-DEC-11 10-DEC-11 Four  Four
10-DEC-11 10-DEC-11 Five  Five

Answer 2

As Mark mentioned in his answer, this is a bug in Oracle 11.2.0.1 and 11.2.0.2 versions atleast.

However as per this article there is a workaround if you are stuck with the Oracle versions mentioned above, which is to convert the date to varchar format and then convert it back to date datatype.

So the query should now be:

WITH 
query AS
(
    SELECT     TO_CHAR(SYSDATE, 'RRRRMMDD') AS SomeDate,
              'One' AS One,
              'Two' AS Two, 
              'Three' AS Three,
              'Four' AS Four,
              'Five' AS Five
        FROM dual
),
up_query AS
(
    SELECT * 
    FROM query
    UNPIVOT 
    ( 
     NUM FOR DUMMY 
     IN 
     ( 
      One AS 'One',
      Two AS 'Two',
      Three AS 'Three',
      Four AS 'Four',
      Five AS 'Five'
     )
    )
)
SELECT SYSDATE, TO_DATE(SomeDate, 'RRRRMMDD') AS ActualSomeDate, b.*, 
  FROM up_query  b;