Pivot statement randomly produces NULL valued columns (Oracle 12c)

I have a query that uses a CTE for pivoting. I run the query without pivoting and the results are always the same.

HOWEVER when I pivot this data from rows to columns (about 15 columns MAX), I keep getting inconsistent results. The pivoted columns data (with exception the last column) appear as NULL. Re-running the same query multiple time in SQL Developer SOMETIMES produces the correct query, probably 1/10 times. I even tried removing the ORDER BY clause at the end of the statement with the same results

This is a very strange error and cannot even reliably reproduce it in order to troubleshoot it. Anyone have an idea as to why is this happening, and how to troubleshoot it?

Some points to keep in mind:

ALL The indexes in the DB were rebuilt recently.
The query is not running in PARALLEL
No DML is occurring on the source of data when running the PIVOT query

Below is a sample of the query that I run:

WITH T1 AS
  (SELECT COL1,
          COL2,
          COL3,
          COL4,
          COL5
   FROM SOURCE_DATA)
SELECT *
FROM
  (SELECT *
   FROM T1 PIVOT(MAX(COL2)
                 FOR (COL3) IN (1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4",
                                5 AS "5", 6 AS "6", 7 AS "7", 9 AS "9",
                               12 AS "12", 16 AS "16", 17 AS "17", 19 AS "19", 
                               21 AS "21", 22 AS "22",23 AS "23"))) ORDER BY COL1 ;

Solution

In my attempt to create a reproducible example for you guys (it was quite complex and wanted to avoid to disclose any system info) I went ahead and rewrote the CTE query before the PIVOT.

No major rewrite, the query logic is the same. Keep in mind that the CTE portion of the original query returned the correct results 100% of the time. The changes to CTE query were getting rid of a UNION operation, and rewriting the query using explicit JOIN syntax, it was originally a mix of implicit and explicit JOIN syntax.

After doing this I confirmed that the results returned by both CTE before the PIVOT are identical! As an added bonus, the execution plan now needs 10 steps instead of 16 as previously.

It seems that modifying the CTE query has caused Oracle to be more consistent in the results being returned after PIVOTING, and so far I have not run into the issue I was facing. The PIVOT now returns the expected results.

Again not exactly sure what caused Oracle to return the correct results after the rewrite without any random NULL valued columns. I guess this may be a bug in Oracle 12c, and the CTE Query rewrite (and subsequently different execution plan being generated) just allowed it to return the correct results.