ORACLE 19c bug in cursor expression

ORACLE 19c, cursor expression & AOP

 

Having issues after Oracle 19c upgrade with AOP reports in APEX?

 

Oracle 12 and Oracle 19c gave different results running a query

Something that happened to us recently that you should be aware of if you recently upgraded to Oracle 19c and if you are using cursor expressions like ones common in APEX Office Print.

This is a query that has been running in production for a while (Oracle 12):

select id as "id",
                 name as "name",   
          cursor(select  rap2.id,
                     rap2.i1 as "i1",
                     rap2.r1 as "r1",
                     rap2.r2 as "r2",
                     rap2.i3 as "i3",
                     rap2.r3 as "r3",
                     rap2.i4 as "i4",
                     rap2.r4 as "r4",
                     rap2.trad_id as "trad_id",
                     rap2.zone as "zone",
                     rap2.wage_code as "wage_code",
                     rap2.jrny_rate as "jrny_rate"
               from v_program_request_access rap2
                where rap2.id = rap1.id   
               ) as "det"                 
           from v_program_request_access rap1
           where rap1.id = 28820
           group by id, name;

After the upgrade to Oracle 19c our clients reported a problem with the above report saying why it is not returning data anymore when we know there is data available? 

After few hours debugging and double checking what is going on there was nothing obvious.  

For some unknown reason query in 12c on same data set was returning

where the same query run on Oracle 19 returned something quite different:


Notice how 19c returned all null values in cursor expression. 

Then Skillbuilders Database expert came onto a stage and actually found more interesting things.

That does give a very different plan in 12 and 19. 12 is materializing the view, 19 is merging it. If I force the 12c plan in 19c, I get a different result.

Bottom line is this was a root cause of our AOP report not return data back into user PDFs as Oracle 19c didn't return data from cursor expression. 

It seems to be a bug in Oracle 19c. Ticket is raised with Oracle to help us sort this issue so keep tuned for more info.

Happy APEXing,
Lino