Forum Stats

  • 3,874,111 Users
  • 2,266,682 Discussions
  • 7,911,728 Comments

Discussions

Cont: Intermittent wrong results with 19.4.0.0.190716

David Balažic
David Balažic Member Posts: 206 Bronze Badge
edited Aug 30, 2020 5:37PM in SQL & PL/SQL

As the topic Intermittent wrong results with 19.4.0.0.190716 is archived and read-only, I'm posting here.

Here is a self contained test case: attached as ZIP


Extract, adapt the database address in doall.sql and run it as : sqlplus /NOLOG @ doall

On 19.4 and 19.7 ( also on livesql.oracle.com ) it prints at the end:

  COUNT(*)

----------

         0                      <----- WRONG

Session altered           <---- apply workaround

PL/SQL procedure successfully completed.   

  COUNT(*)

----------

         1                  <------ CORRECT

Jonathan Lewis
«1

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Aug 7, 2020 10:13AM
    David Balažic wrote:As the topic Intermittent wrong results with 19.4.0.0.190716 is archived and read-only, I'm posting here.Here is a self contained test case: attached as ZIP
    Extract, adapt the database address in doall.sql and run it as : sqlplus /NOLOG @ doallOn 19.4 and 19.7 ( also on livesql.oracle.com ) it prints at the end: COUNT(*)---------- 0 <----- WRONGSession altered <---- apply workaroundPL/SQL procedure successfully completed. COUNT(*)---------- 1 <------ CORRECT

    Looks like a great test case to supply Oracle support with.

    Could you expand on what you're looking for the forum members to contribute here? Do you need help rewriting the SQL?

    Cheers,

  • David Balažic
    David Balažic Member Posts: 206 Bronze Badge
    edited Aug 7, 2020 10:17AM

    I'm just adding more information to the old thread. It might be interesting to the participants.

    Oracle support is already working on it.

  • David Balažic
    David Balažic Member Posts: 206 Bronze Badge
    edited Aug 7, 2020 12:04PM

    Just a ping to (some) participants of the old thread...

    @Jonathan Lewis

    @AndrewSayer

    @Cookiemonster76

    Jonathan Lewis
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Aug 8, 2020 2:30AM

    I've just run up your model in 19.3 and got the results you predicted.

    You have a virtual column in the subquery - so one little test I did was to replace the virtual column with the definition of the virtual column, and when I did that setting optimizer_features_enable= '11.2.0.4' got me the wrong answer (zero) as well.

    Three options:

    a) 11.2.0.4 is showing the wrong answer (1) in this case and 19.3 is right

    b) 11.2.0.4 and 19.3 are handling the virtual column differently internally so generate different values (e.g. null vs. non-null).

    c)  I've made a mistake by thinking it's valid to replace the virtual column with its definition in this outer join with OR subquery case.

    UPDATE:

    and when I did that with the correct virtual column definition, 19.3 gave the right answer consistently.

    There was only one significant difference I noted, the projection information for operation 3 (the first child of the filter):

    With virtual column

       3 - (#keys=1) "TR"."RID"[NUMBER,22], ROWID[ROWID,10], "RID"[NUMBER,22], "FLAG"[CHARACTER,1], "TR"."FLAG"[CHARACTER,1]

    With actual definition

       3 - (#keys=1) "TR"."RID"[NUMBER,22], "TR"."RID"[NUMBER,22], "TR"."FLAG"[CHARACTER,1], "TR"."FLAG"[CHARACTER,1]

    FInally with optimizer_features_enable = 11g, hinting the same plan and using the virtual column:

       3 - (#keys=1) "TR"."RID"[NUMBER,22], "FLAG"[CHARACTER,1]

    Regards

    Jonathan Lewis

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Aug 10, 2020 8:20AM

    Just a quick update.

    I've had an email that this is now logged on MOS as

    Bug 31732779 - WRONG RESULT WITH CASE STATEMENT AGGREGATION

    Not yet visible to public.

    Regards

    Jonathan Lewis

  • David Balažic
    David Balažic Member Posts: 206 Bronze Badge
    edited Aug 10, 2020 8:24AM

    I have a SR open for this issue, I would expect the bug to be logged by now... strange.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Aug 10, 2020 9:24AM

    I would add the bug number to the SR - it's only been added in the last few hours, so your support analyst may not have seen it when they were checking.

    Regards

    Jonathan Lewis

  • David Balažic
    David Balažic Member Posts: 206 Bronze Badge
    edited Aug 10, 2020 9:40AM

    What I mean is: the support analyst has more information about the issue than you, for much longer time. So I would expect him to file the bug.

    Will add the bug to the SR...

  • David Balažic
    David Balažic Member Posts: 206 Bronze Badge
    edited Aug 30, 2020 5:37PM

    Understanding that each case is different, what is typically the timeframe of fixing wrong result bugs? As in "statistics over past cases" ?

  • David Balažic
    David Balažic Member Posts: 206 Bronze Badge

    On https://jonathanlewis.wordpress.com/2020/08/06/case-and-aggregate-bug/ you wrote [Fixed by 19.11.0.0] but my testcase (see first post) still behaves the same: wrong result, unless setting OPTIMIZER_FEATURES_ENABLE='11.2.0.4'

    I just tried on livesql.oracle.com, (v$version says 19.14.0.0.0).