Oracle UNPIVOT and SYSDATE giving weird results
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;

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

$ 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

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;