Some bugs in Oracle’s code are really surprising. Whenever I run into this kind of issue, I can’t help but wonder how nobody else noticed it before.

Some days ago I was querying AWR data from DBA_HIST_SYSMETRIC_SUMMARY and I wanted to turn the columns AVERAGE, MAXVAL and MINVAL into rows, in order to fit this result set into a performance graphing application that expects input data formatted as {TimeStamp, SeriesName, Value}.

Columns to rows? A good match for UNPIVOT.

Oracle 11g introduced PIVOT and UNPIVOT operators to allow rows-to-columns and columns-to-rows transformations. Prior to 11g, this kind of transformation had to be coded with bulky CASE expressions (for PIVOT) or pesky UNION queries (for UNPIVOT). PIVOT and UNPIVOT allow developers to write more concise and readable statements, but I guess that not so many people have been using these features since their release, or they would have found this bug very soon.

Here is the statement I was trying to run:

WITH Metrics AS (
    SELECT to_date(to_char(BEGIN_TIME,'YYYY-MM-DD HH24'),'YYYY-MM-DD HH24') AS TS,
        AVG(AVERAGE) AS AVERAGE,
        MAX(MAXVAL) AS MAXVAL,
        MIN(MINVAL) AS MINVAL
    FROM DBA_HIST_SYSMETRIC_SUMMARY
    WHERE METRIC_NAME = 'Host CPU Utilization (%)'
    GROUP BY to_date(to_char(BEGIN_TIME,'YYYY-MM-DD HH24'),'YYYY-MM-DD HH24')
)
SELECT TS, aggregate, value
FROM Metrics
UNPIVOT (value FOR aggregate IN (AVERAGE, MAXVAL, MINVAL))
ORDER BY 1

The idea behind was to convert the date column into a string without the minute part, in order to convert it back to date and group by hour.

Surprisingly enough, this was the result:

The date column was returned with wrong data. Why?

The issue seems to be related to the date datatype, because converting back to date after the UNPIVOT works just fine:

WITH Metrics AS (
    SELECT to_char(BEGIN_TIME,'YYYY-MM-DD HH24') AS TS,
        AVG(AVERAGE) AS AVERAGE,
        MAX(MAXVAL) AS MAXVAL,
        MIN(MINVAL) AS MINVAL
    FROM DBA_HIST_SYSMETRIC_SUMMARY
    WHERE METRIC_NAME = 'Host CPU Utilization (%)'
    GROUP BY to_char(BEGIN_TIME,'YYYY-MM-DD HH24')
)
SELECT to_date(TS,'YYYY-MM-DD HH24') AS TS, aggregate, value
FROM Metrics
UNPIVOT (value FOR aggregate IN (AVERAGE, MAXVAL, MINVAL))
ORDER BY 1

This query, instead, produces the expected results.

I raised this issue with Oracle Support who filed it under bug ID 9900850.8. Both 11.2.0.1 and 11.2.0.2 seem to be be affected by this problem, but it’s quite unlikely to see it fixed before 12.1.

Time will tell.