Oracle 12.2 - Wrong output in query with NESTED LOOPS and ANTI JOIN

Question

I found this strange behaviour in Oracle 12cR2 ( over Linux Red Hat 7 ) with this particular query. It looks to me like it is a bug, but I post the question just in case someone has found a similar issue.

The query is triggered by a Java Application, but I have been able to reproduce the problem in sqlplus.

Tip: Don't ask me about the excessive number of parenthesis, I did it myself and it is coming from the application itself, so I use the same sql text to be 100% accurate.

Query 1

SQL> SELECT *
  2  from (SELECT
  3          MiscellaneousInformation.miscInfoNo,
  4          null AS thirdPartyNumber,
  5          COALESCE(ProposalAgreement.id,
  6          Agreement.id,
  7          0) AS alfaAgreementIdentifier,
  8          COALESCE(ProposalSchedule.id,
  9          Schedule.id,
 10          0) AS alfaScheduleIdentifier,
 11          COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
 12          --ProposalAgreement.agreementNumber an1, Agreement.agreementNumber an2,
 13          ProposalAgreement.agreementNumber AS AGREEMENTNUMBER0,
 14             --CASE WHEN Agreement.agreementNumber IS NOT NULL THEN  Agreement.agreementNumber  ELSE ProposalAgreement.agreementNumber END AS AGREEMENTNUMBER,
 15          COALESCE(Schedule.scheduleNumber,
 16          ProposalSchedule.scheduleNumber,
 17          0),
 18          COALESCE(Schedule.terminationNumber,
 19          ProposalSchedule.terminationNumber,
 20          0),
 21          0 AS lineOfCreditNumber,
 22          0 AS securityIdentifier,
 23          0 AS caseIdSequenceNumber,
 24          MiscellaneousInformation.informationType,
 25          MiscellaneousInformation.detail,
 26          MiscellaneousInformation.valueAmount,
 27          MiscellaneousInformation.dateField,
 28          MiscellaneousInformation.valueCurrency,
 29          MiscellaneousInfoListValue.description,
 30          MiscellaneousInformation.ownerDiscriminator,
 31          MiscellaneousInformation.ownerEntityId
 32      FROM
 33          ALFATS.MiscellaneousInformation
 34      LEFT OUTER JOIN
 35          ALFATS.MiscellaneousInfoListValue
 36              ON (
 37                  (
 38                      MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
 39                  )
 40                  AND (
 41                      MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
 42                  )
 43              )
 44      LEFT OUTER JOIN
 45          ALFATS.ScheduleEntity
 46              ON (
 47                  MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
 48              )
 49      LEFT OUTER JOIN
 50          ALFATS.Schedule
 51              ON (
 52                  (
 53                      Schedule.id = ScheduleEntity.primaryScheduleId
 54                  )
 55                  AND (
 56                      Schedule.terminationNumber IN (
 57                          0,
 58                      1)))
 59                  LEFT OUTER JOIN
 60                      ALFATS.ProposalSchedule
 61                          ON (
 62                              MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
 63                          )
 64                  LEFT OUTER JOIN
 65                      ALFATS.Agreement
 66                          ON (
 67                              (
 68                                  MiscellaneousInformation.ownerEntityId = Agreement.entityId
 69                              )
 70                              OR (
 71                                  Schedule.agreementNumber = Agreement.agreementNumber
 72                              )
 73                              OR (
 74                                  ProposalSchedule.agreementNumber = Agreement.agreementNumber
 75                              )
 76                          )
 77                  LEFT OUTER JOIN
 78                      ALFATS.ProposalAgreement
 79                          ON (
 80                              (
 81                                  MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
 82                              )
 83                              OR (
 84                                  ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
 85                              )
 86                          )
 87                  WHERE
 88                      (
 89                          (
 90                              (
 91                                  (
 92                                      MiscellaneousInformation.ownerDiscriminator = N'AGR'
 93                                  )
 94                                  OR (
 95                                      MiscellaneousInformation.ownerDiscriminator = N'SCH'
 96                                  )
 97                              )
 98                              AND (
 99                                  NOT (EXISTS (SELECT
100                                      1
101                                  FROM
102                                      ALFATS.MiscellaneousInformation miscInfoInner
103                                  WHERE
104                                      ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
105                                      AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
106                                      AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
107                              )
108                          )                        AND (
109                              MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
110                          )
111                      ) )
112  WHERE  OWNERDISCRIMINATOR = 'AGR'
113  AND agreementnumber IS not NULL
114  --AND agreementnumber0 IS NOT NULL
115*

no rows selected

Elapsed: 00:03:07.56

Execution Plan
----------------------------------------------------------
Plan hash value: 70829564

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |   671 |   370K|   210K  (1)| 00:00:09 |
|*  1 |  FILTER                            |                             |       |       |            |          |
|   2 |   MERGE JOIN OUTER                 |                             |   671 |   370K|   210K  (1)| 00:00:09 |
|   3 |    MERGE JOIN OUTER                |                             |   336 |   173K|   208K  (1)| 00:00:09 |
|   4 |     NESTED LOOPS OUTER             |                             |   112 | 55440 |   207K  (1)| 00:00:09 |
|   5 |      NESTED LOOPS OUTER            |                             |   112 | 51296 |   207K  (1)| 00:00:09 |
|*  6 |       HASH JOIN OUTER              |                             |   112 | 42448 |   206K  (1)| 00:00:09 |
|   7 |        NESTED LOOPS OUTER          |                             |   112 | 35280 |   206K  (1)| 00:00:09 |
|   8 |         NESTED LOOPS ANTI          |                             |   112 | 23072 |   206K  (1)| 00:00:09 |
|*  9 |          TABLE ACCESS FULL         | MISCELLANEOUSINFORMATION    | 11184 |  1299K|   173K  (1)| 00:00:07 |
|* 10 |          INDEX RANGE SCAN          | MISCELLANEOUSINFORMATION_L2 |    13M|  1135M|     3   (0)| 00:00:01 |
|  11 |         TABLE ACCESS BY INDEX ROWID| PROPOSALSCHEDULE            |     1 |   109 |     2   (0)| 00:00:01 |
|* 12 |          INDEX UNIQUE SCAN         | PROPOSALSCHEDULE_N2         |     1 |       |     1   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL           | MISCELLANEOUSINFOLISTVALUE  |   699 | 44736 |     6   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID  | SCHEDULEENTITY              |     1 |    79 |     2   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN           | SCHEDULEENTITY_N2           |     1 |       |     1   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS BY INDEX ROWID   | SCHEDULE                    |     1 |    37 |     1   (0)| 00:00:01 |
|* 17 |       INDEX UNIQUE SCAN            | SCHEDULE_PK                 |     1 |       |     0   (0)| 00:00:01 |
|  18 |     BUFFER SORT                    |                             |     3 |   105 |   208K  (1)| 00:00:09 |
|  19 |      VIEW                          | VW_LAT_B4E6951E             |     3 |   105 |     9   (0)| 00:00:01 |
|  20 |       VIEW                         | VW_ORE_A774FCAE             |     3 |   105 |     9   (0)| 00:00:01 |
|  21 |        UNION-ALL                   |                             |       |       |            |          |
|  22 |         TABLE ACCESS BY INDEX ROWID| AGREEMENT                   |     1 |   104 |     3   (0)| 00:00:01 |
|* 23 |          INDEX UNIQUE SCAN         | AGREEMENT_N2                |     1 |       |     2   (0)| 00:00:01 |
|* 24 |         TABLE ACCESS BY INDEX ROWID| AGREEMENT                   |     1 |   104 |     3   (0)| 00:00:01 |
|* 25 |          INDEX UNIQUE SCAN         | AGREEMENT_NK                |     1 |       |     2   (0)| 00:00:01 |
|* 26 |         TABLE ACCESS BY INDEX ROWID| AGREEMENT                   |     1 |   104 |     3   (0)| 00:00:01 |
|* 27 |          INDEX UNIQUE SCAN         | AGREEMENT_NK                |     1 |       |     2   (0)| 00:00:01 |
|  28 |    BUFFER SORT                     |                             |     2 |    70 |   210K  (1)| 00:00:09 |
|  29 |     VIEW                           | VW_LAT_B4E6951E             |     2 |    70 |     6   (0)| 00:00:01 |
|  30 |      VIEW                          | VW_ORE_E1C15686             |     2 |    70 |     6   (0)| 00:00:01 |
|  31 |       UNION-ALL                    |                             |       |       |            |          |
|  32 |        TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT           |     1 |   104 |     3   (0)| 00:00:01 |
|* 33 |         INDEX UNIQUE SCAN          | PROPOSALAGREEMENT_N2        |     1 |       |     2   (0)| 00:00:01 |
|* 34 |        TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT           |     1 |   104 |     3   (0)| 00:00:01 |
|* 35 |         INDEX UNIQUE SCAN          | PROPOSALAGREEMENT_NK        |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
   6 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(
              +) AND "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
   9 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
              MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
  10 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
              "MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
              "MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
  12 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
  15 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
  16 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
  17 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
  23 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
  24 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  25 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
  26 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  27 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
       filter(LNNVL("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
  33 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
  34 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
  35 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
    1319465  consistent gets
     931422  physical reads
          0  redo size
       1846  bytes sent via SQL*Net to client
        597  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
     112034  sorts (memory)
          0  sorts (disk)
          0  rows processed

So the query 1 returns 0 rows. Well, let's just change the select * from for just to get three columns of the table.

Query 2

SQL>   1  SELECT ownerDiscriminator , agreementnumber, agreementnumber0
  2  from (SELECT
  3          MiscellaneousInformation.miscInfoNo,
  4          null AS thirdPartyNumber,
  5          COALESCE(ProposalAgreement.id,
  6          Agreement.id,
  7          0) AS alfaAgreementIdentifier,
  8          COALESCE(ProposalSchedule.id,
  9          Schedule.id,
 10          0) AS alfaScheduleIdentifier,
 11          COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
 12          --ProposalAgreement.agreementNumber an1, Agreement.agreementNumber an2,
 13          ProposalAgreement.agreementNumber AS AGREEMENTNUMBER0,
 14             --CASE WHEN Agreement.agreementNumber IS NOT NULL THEN  Agreement.agreementNumber  ELSE ProposalAgreement.agreementNumber END AS AGREEMENTNUMBER,
 15          COALESCE(Schedule.scheduleNumber,
 16          ProposalSchedule.scheduleNumber,
 17          0),
 18          COALESCE(Schedule.terminationNumber,
 19          ProposalSchedule.terminationNumber,
 20          0),
 21          0 AS lineOfCreditNumber,
 22          0 AS securityIdentifier,
 23          0 AS caseIdSequenceNumber,
 24          MiscellaneousInformation.informationType,
 25          MiscellaneousInformation.detail,
 26          MiscellaneousInformation.valueAmount,
 27          MiscellaneousInformation.dateField,
 28          MiscellaneousInformation.valueCurrency,
 29          MiscellaneousInfoListValue.description,
 30          MiscellaneousInformation.ownerDiscriminator,
 31          MiscellaneousInformation.ownerEntityId
 32      FROM
 33          ALFATS.MiscellaneousInformation
 34      LEFT OUTER JOIN
 35          ALFATS.MiscellaneousInfoListValue
 36              ON (
 37                  (
 38                      MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
 39                  )
 40                  AND (
 41                      MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
 42                  )
 43              )
 44      LEFT OUTER JOIN
 45          ALFATS.ScheduleEntity
 46              ON (
 47                  MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
 48              )
 49      LEFT OUTER JOIN
 50          ALFATS.Schedule
 51              ON (
 52                  (
 53                      Schedule.id = ScheduleEntity.primaryScheduleId
 54                  )
 55                  AND (
 56                      Schedule.terminationNumber IN (
 57                          0,
 58                      1)))
 59                  LEFT OUTER JOIN
 60                      ALFATS.ProposalSchedule
 61                          ON (
 62                              MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
 63                          )
 64                  LEFT OUTER JOIN
 65                      ALFATS.Agreement
 66                          ON (
 67                              (
 68                                  MiscellaneousInformation.ownerEntityId = Agreement.entityId
 69                              )
 70                              OR (
 71                                  Schedule.agreementNumber = Agreement.agreementNumber
 72                              )
 73                              OR (
 74                                  ProposalSchedule.agreementNumber = Agreement.agreementNumber
 75                              )
 76                          )
 77                  LEFT OUTER JOIN
 78                      ALFATS.ProposalAgreement
 79                          ON (
 80                              (
 81                                  MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
 82                              )
 83                              OR (
 84                                  ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
 85                              )
 86                          )
 87                  WHERE
 88                      (
 89                          (
 90                              (
 91                                  (
 92                                      MiscellaneousInformation.ownerDiscriminator = N'AGR'
 93                                  )
 94                                  OR (
 95                                      MiscellaneousInformation.ownerDiscriminator = N'SCH'
 96                                  )
 97                              )
 98                              AND (
 99                                  NOT (EXISTS (SELECT
100                                      1
101                                  FROM
102                                      ALFATS.MiscellaneousInformation miscInfoInner
103                                  WHERE
104                                      ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
105                                      AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
106                                      AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
107                              )
108                          )                        AND (
109                              MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
110                          )
111                      ) )
112  WHERE  OWNERDISCRIMINATOR = 'AGR'
113  AND agreementnumber IS not NULL
114* --AND agreementnumber0 IS NOT NULL 
115 ;

56017 rows selected.

Elapsed: 00:00:25.63

Execution Plan
----------------------------------------------------------
Plan hash value: 2355278442

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |   671 |   309K|   210K  (1)| 00:00:09 |
|*  1 |  FILTER                            |                              |       |       |            |          |
|   2 |   MERGE JOIN OUTER                 |                              |   671 |   309K|   210K  (1)| 00:00:09 |
|   3 |    MERGE JOIN OUTER                |                              |   336 |   147K|   208K  (1)| 00:00:09 |
|   4 |     NESTED LOOPS OUTER             |                              |   112 | 47936 |   207K  (1)| 00:00:09 |
|   5 |      NESTED LOOPS OUTER            |                              |   112 | 44128 |   207K  (1)| 00:00:09 |
|*  6 |       HASH JOIN RIGHT OUTER        |                              |   112 | 35280 |   206K  (1)| 00:00:09 |
|   7 |        INDEX FAST FULL SCAN        | MISCELLANEOUSINFOLISTVALUE_1 |   699 | 13281 |     3   (0)| 00:00:01 |
|   8 |        NESTED LOOPS OUTER          |                              |   112 | 33152 |   206K  (1)| 00:00:09 |
|   9 |         NESTED LOOPS ANTI          |                              |   112 | 22176 |   206K  (1)| 00:00:09 |
|* 10 |          TABLE ACCESS FULL         | MISCELLANEOUSINFORMATION     | 11184 |  1212K|   173K  (1)| 00:00:07 |
|* 11 |          INDEX RANGE SCAN          | MISCELLANEOUSINFORMATION_L2  |    13M|  1135M|     3   (0)| 00:00:01 |
|  12 |         TABLE ACCESS BY INDEX ROWID| PROPOSALSCHEDULE             |     1 |    98 |     2   (0)| 00:00:01 |
|* 13 |          INDEX UNIQUE SCAN         | PROPOSALSCHEDULE_N2          |     1 |       |     1   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID  | SCHEDULEENTITY               |     1 |    79 |     2   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN           | SCHEDULEENTITY_N2            |     1 |       |     1   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS BY INDEX ROWID   | SCHEDULE                     |     1 |    34 |     1   (0)| 00:00:01 |
|* 17 |       INDEX UNIQUE SCAN            | SCHEDULE_PK                  |     1 |       |     0   (0)| 00:00:01 |
|  18 |     BUFFER SORT                    |                              |     3 |    66 |   208K  (1)| 00:00:09 |
|  19 |      VIEW                          | VW_LAT_B4E6951E              |     3 |    66 |     9   (0)| 00:00:01 |
|  20 |       VIEW                         | VW_ORE_A774FCAE              |     3 |    66 |     9   (0)| 00:00:01 |
|  21 |        UNION-ALL                   |                              |       |       |            |          |
|  22 |         TABLE ACCESS BY INDEX ROWID| AGREEMENT                    |     1 |    98 |     3   (0)| 00:00:01 |
|* 23 |          INDEX UNIQUE SCAN         | AGREEMENT_N2                 |     1 |       |     2   (0)| 00:00:01 |
|* 24 |         TABLE ACCESS BY INDEX ROWID| AGREEMENT                    |     1 |    98 |     3   (0)| 00:00:01 |
|* 25 |          INDEX UNIQUE SCAN         | AGREEMENT_NK                 |     1 |       |     2   (0)| 00:00:01 |
|* 26 |         TABLE ACCESS BY INDEX ROWID| AGREEMENT                    |     1 |    98 |     3   (0)| 00:00:01 |
|* 27 |          INDEX UNIQUE SCAN         | AGREEMENT_NK                 |     1 |       |     2   (0)| 00:00:01 |
|  28 |    BUFFER SORT                     |                              |     2 |    44 |   210K  (1)| 00:00:09 |
|  29 |     VIEW                           | VW_LAT_B4E6951E              |     2 |    44 |     6   (0)| 00:00:01 |
|  30 |      VIEW                          | VW_ORE_E1C15686              |     2 |    44 |     6   (0)| 00:00:01 |
|  31 |       UNION-ALL                    |                              |       |       |            |          |
|  32 |        TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT            |     1 |    98 |     3   (0)| 00:00:01 |
|* 33 |         INDEX UNIQUE SCAN          | PROPOSALAGREEMENT_N2         |     1 |       |     2   (0)| 00:00:01 |
|* 34 |        TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT            |     1 |    98 |     3   (0)| 00:00:01 |
|* 35 |         INDEX UNIQUE SCAN          | PROPOSALAGREEMENT_NK         |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
   6 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(+
              ) AND "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
  10 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
              MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
  11 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
              "MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
              "MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
  13 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
  15 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
  16 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
  17 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
  23 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
  24 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  25 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
  26 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  27 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
       filter(LNNVL("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
  33 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
  34 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
  35 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
    1437831  consistent gets
     637478  physical reads
          0  redo size
    2796818  bytes sent via SQL*Net to client
      41682  bytes received via SQL*Net from client
       3736  SQL*Net roundtrips to/from client
     112034  sorts (memory)
          0  sorts (disk)
      56017  rows processed

How is this even possible ? Am I not losing my mind, right ?

As is a testing environment, I did flush buffer cache and shared pool, checked looking for corruption and even bounce the database. So far, nothing.

Short summary, using select * from gives me 0 rows, using select field, field, field from gives me the right number of rows.

Do you think that this is a bug or can be something different which I am not considering ?

P.S. adding an order by also changes the number of rows returned.

UPDATE

Moving the table segments and rebuilding all their indexes did not solve anything either. I tried even using datapump to export the schema, drop the tablespace, rebuild the tablespace in a different ASM disk and importing back. No effect.

So, it is clear to me that this is a BUG in 12.2, but I can't find exactly which one.

it is a bug, for sure. I can't find which one, though. however, changing `optimizer_featuresd_enabled` to 12.1 solves the strange behaviour. — Roberto Hernandez, Aug 27 '21 at 13:48
It would be interesting to simplify this down to the shortest example that reproduces the issue, as then the pattern might become clearer. — William Robertson, Aug 27 '21 at 17:19
@WilliamRobertson , you read my mind. I am going step by step, but I think it has something to do with a couple of indexes involved in the nested loops. Modifying `OPTIMIZER_INDEX_CACHING` to 100 make the query works too, and in this case I would prefer to use it for this query at session level rather than manipulating `optimizer_features_enabled`. As soon as I have everything, I will publish the answer with all details. I hope I can find the bug number. — Roberto Hernandez, Aug 28 '21 at 08:28
If the difference between the execution plans is related to an index, perhaps the problem is index corruption instead of a wrong-results bug. It's a rare problem, but I've seen it happen a few times. Try rebuilding the indexes. — Jon Heller, Aug 28 '21 at 17:40
@JonHeller, I tried that and nothing Only changing optimizer values affect the result of the query which leads me to believe it is a bug. — Roberto Hernandez, Aug 30 '21 at 06:30

score 0 · Accepted Answer · answered Aug 31 '21 at 11:41

I post this answer, although more like an answer I'd define it like a workaround to the issue. But I believe it is worth it just in case someone finds himself in this issue. I could not find the problematic index ( or indexes ), but let me show you how the hints over the optimizer solve the wrong output result.

Original Query

When you use select * the output is 0 rows. If you use some fields only in the outer select, the result is ok.

Workaround 1

Changing optimizer_features_enabled to version 12.1 does the trick.

SQL> alter session set optimizer_features_enable='12.1.0.2';

Session altered.

Elapsed: 00:00:00.01
SQL> SELECT *
  2  from (SELECT
  3          MiscellaneousInformation.miscInfoNo,
  4          null AS thirdPartyNumber,
  5          COALESCE(ProposalAgreement.id,
  6          Agreement.id,
  7          0) AS alfaAgreementIdentifier,
  8          COALESCE(ProposalSchedule.id,
  9          Schedule.id,
 10          0) AS alfaScheduleIdentifier,
 11          COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
 12          COALESCE(Schedule.scheduleNumber,
 13          ProposalSchedule.scheduleNumber,
 14          0) as scheduleNumber,
 15          COALESCE(Schedule.terminationNumber,
 16          ProposalSchedule.terminationNumber,
 17          0) as terminationNumber,
 18          0 AS lineOfCreditNumber,
 19          0 AS securityIdentifier,
 20          0 AS caseIdSequenceNumber,
 21          MiscellaneousInformation.informationType,
 22          MiscellaneousInformation.detail,
 23          MiscellaneousInformation.valueAmount,
 24          MiscellaneousInformation.dateField,
 25          MiscellaneousInformation.valueCurrency,
 26          MiscellaneousInfoListValue.description,
 27          MiscellaneousInformation.ownerDiscriminator,
 28          MiscellaneousInformation.ownerEntityId
 29      FROM
 30          ALFATS.MiscellaneousInformation
 31      LEFT OUTER JOIN
 32          ALFATS.MiscellaneousInfoListValue
 33              ON (
 34                  (
 35                      MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
 36                  )
 37                  AND (
 38                      MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
 39                  )
 40              )
 41      LEFT OUTER JOIN
 42          ALFATS.ScheduleEntity
 43              ON (
 44                  MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
 45              )
 46      LEFT OUTER JOIN
 47          ALFATS.Schedule
 48              ON (
 49                  (
 50                      Schedule.id = ScheduleEntity.primaryScheduleId
 51                  )
 52                  AND (
 53                      Schedule.terminationNumber IN (
 54                          0,
 55                      1)))
 56                  LEFT OUTER JOIN
 57                      ALFATS.ProposalSchedule
 58                          ON (
 59                              MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
 60                          )
 61                  LEFT OUTER JOIN
 62                      ALFATS.Agreement
 63                          ON (
 64                              (
 65                                  MiscellaneousInformation.ownerEntityId = Agreement.entityId
 66                              )
 67                              OR (
 68                                  Schedule.agreementNumber = Agreement.agreementNumber
 69                              )
 70                              OR (
 71                                  ProposalSchedule.agreementNumber = Agreement.agreementNumber
 72                              )
 73                          )
 74                  LEFT OUTER JOIN
 75                      ALFATS.ProposalAgreement
 76                          ON (
 77                              (
 78                                  MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
 79                              )
 80                              OR (
 81                                  ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
 82                              )
 83                          )
 84                  WHERE
 85                      (
 86                          (
 87                              (
 88                                  (
 89                                      MiscellaneousInformation.ownerDiscriminator = N'AGR'
 90                                  )
 91                                  OR (
 92                                      MiscellaneousInformation.ownerDiscriminator = N'SCH'
 93                                  )
 94                              )
 95                              AND (
 96                                  NOT (EXISTS (SELECT
 97                                      1
 98                                  FROM
 99                                      ALFATS.MiscellaneousInformation miscInfoInner
100                                  WHERE
101                                      ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
102                                      AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
103                                      AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
104                              )
105                          )                        AND (
106                              MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
107                          )
108                      ) )
109  WHERE  OWNERDISCRIMINATOR = 'AGR'
110  AND agreementnumber IS not NULL
111  ;

56011 rows selected.

Elapsed: 00:00:23.28

Execution Plan
----------------------------------------------------------
Plan hash value: 1394098322

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |   671 |   370K|   213K  (1)| 00:00:09 |
|*  1 |  FILTER                            |                             |       |       |            |          |
|   2 |   NESTED LOOPS OUTER               |                             |   671 |   370K|   213K  (1)| 00:00:09 |
|   3 |    NESTED LOOPS OUTER              |                             |   335 |   173K|   211K  (1)| 00:00:09 |
|   4 |     NESTED LOOPS OUTER             |                             |   112 | 55440 |   210K  (1)| 00:00:09 |
|   5 |      NESTED LOOPS OUTER            |                             |   112 | 51296 |   210K  (1)| 00:00:09 |
|*  6 |       HASH JOIN OUTER              |                             |   112 | 42448 |   210K  (1)| 00:00:09 |
|   7 |        NESTED LOOPS OUTER          |                             |   112 | 35280 |   210K  (1)| 00:00:09 |
|   8 |         NESTED LOOPS ANTI          |                             |   112 | 23072 |   210K  (1)| 00:00:09 |
|*  9 |          TABLE ACCESS FULL         | MISCELLANEOUSINFORMATION    | 11178 |  1299K|   176K  (1)| 00:00:07 |
|* 10 |          INDEX RANGE SCAN          | MISCELLANEOUSINFORMATION_L2 |    13M|  1132M|     3   (0)| 00:00:01 |
|  11 |         TABLE ACCESS BY INDEX ROWID| PROPOSALSCHEDULE            |     1 |   109 |     2   (0)| 00:00:01 |
|* 12 |          INDEX UNIQUE SCAN         | PROPOSALSCHEDULE_N2         |     1 |       |     1   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL           | MISCELLANEOUSINFOLISTVALUE  |   699 | 44736 |     5   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID  | SCHEDULEENTITY              |     1 |    79 |     2   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN           | SCHEDULEENTITY_N2           |     1 |       |     1   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS BY INDEX ROWID   | SCHEDULE                    |     1 |    37 |     1   (0)| 00:00:01 |
|* 17 |       INDEX UNIQUE SCAN            | SCHEDULE_PK                 |     1 |       |     0   (0)| 00:00:01 |
|  18 |     VIEW                           | VW_LAT_B4E6951E             |     3 |   105 |     9   (0)| 00:00:01 |
|  19 |      CONCATENATION                 |                             |       |       |            |          |
|  20 |       TABLE ACCESS BY INDEX ROWID  | AGREEMENT                   |     1 |   104 |     3   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN           | AGREEMENT_N2                |     1 |       |     2   (0)| 00:00:01 |
|* 22 |       TABLE ACCESS BY INDEX ROWID  | AGREEMENT                   |     1 |   104 |     3   (0)| 00:00:01 |
|* 23 |        INDEX UNIQUE SCAN           | AGREEMENT_NK                |     1 |       |     2   (0)| 00:00:01 |
|* 24 |       TABLE ACCESS BY INDEX ROWID  | AGREEMENT                   |     1 |   104 |     3   (0)| 00:00:01 |
|* 25 |        INDEX UNIQUE SCAN           | AGREEMENT_NK                |     1 |       |     2   (0)| 00:00:01 |
|  26 |    VIEW                            | VW_LAT_B4E6951E             |     2 |    70 |     5   (0)| 00:00:01 |
|  27 |     CONCATENATION                  |                             |       |       |            |          |
|  28 |      TABLE ACCESS BY INDEX ROWID   | PROPOSALAGREEMENT           |     1 |   104 |     3   (0)| 00:00:01 |
|* 29 |       INDEX UNIQUE SCAN            | PROPOSALAGREEMENT_N2        |     1 |       |     2   (0)| 00:00:01 |
|* 30 |      TABLE ACCESS BY INDEX ROWID   | PROPOSALAGREEMENT           |     1 |   104 |     2   (0)| 00:00:01 |
|* 31 |       INDEX UNIQUE SCAN            | PROPOSALAGREEMENT_NK        |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
   6 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(
              +) AND "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
   9 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
              MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
  10 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
              "MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
              "MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
  12 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
  15 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
  16 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
  17 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
  21 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
  22 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  23 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
  24 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  25 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
       filter(LNNVL("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
  29 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
  30 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
  31 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")


Statistics
----------------------------------------------------------
        385  recursive calls
          3  db block gets
    1437939  consistent gets
     648378  physical reads
        132  redo size
   10579547  bytes sent via SQL*Net to client
      41681  bytes received via SQL*Net from client
       3736  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      56011  rows processed

Workaround 2

Changing optimizer_index_caching to 100 solves the problem too, which lead me to believe that the bug might be related with some of the nested loop joins performed by the query.

SQL> alter session set OPTIMIZER_INDEX_CACHING=100;

Session altered.

Elapsed: 00:00:00.00
SQL> SELECT *
  2  from (SELECT
  3          MiscellaneousInformation.miscInfoNo,
  4          null AS thirdPartyNumber,
  5          COALESCE(ProposalAgreement.id,
  6          Agreement.id,
  7          0) AS alfaAgreementIdentifier,
  8          COALESCE(ProposalSchedule.id,
  9          Schedule.id,
 10          0) AS alfaScheduleIdentifier,
 11          COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
 12          COALESCE(Schedule.scheduleNumber,
 13          ProposalSchedule.scheduleNumber,
 14          0) as scheduleNumber,
 15          COALESCE(Schedule.terminationNumber,
 16          ProposalSchedule.terminationNumber,
 17          0) as terminationNumber,
 18          0 AS lineOfCreditNumber,
 19          0 AS securityIdentifier,
 20          0 AS caseIdSequenceNumber,
 21          MiscellaneousInformation.informationType,
 22          MiscellaneousInformation.detail,
 23          MiscellaneousInformation.valueAmount,
 24          MiscellaneousInformation.dateField,
 25          MiscellaneousInformation.valueCurrency,
 26          MiscellaneousInfoListValue.description,
 27          MiscellaneousInformation.ownerDiscriminator,
 28          MiscellaneousInformation.ownerEntityId
 29      FROM
 30          ALFATS.MiscellaneousInformation
 31      LEFT OUTER JOIN
 32          ALFATS.MiscellaneousInfoListValue
 33              ON (
 34                  (
 35                      MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
 36                  )
 37                  AND (
 38                      MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
 39                  )
 40              )
 41      LEFT OUTER JOIN
 42          ALFATS.ScheduleEntity
 43              ON (
 44                  MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
 45              )
 46      LEFT OUTER JOIN
 47          ALFATS.Schedule
 48              ON (
 49                  (
 50                      Schedule.id = ScheduleEntity.primaryScheduleId
 51                  )
 52                  AND (
 53                      Schedule.terminationNumber IN (
 54                          0,
 55                      1)))
 56                  LEFT OUTER JOIN
 57                      ALFATS.ProposalSchedule
 58                          ON (
 59                              MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
 60                          )
 61                  LEFT OUTER JOIN
 62                      ALFATS.Agreement
 63                          ON (
 64                              (
 65                                  MiscellaneousInformation.ownerEntityId = Agreement.entityId
 66                              )
 67                              OR (
 68                                  Schedule.agreementNumber = Agreement.agreementNumber
 69                              )
 70                              OR (
 71                                  ProposalSchedule.agreementNumber = Agreement.agreementNumber
 72                              )
 73                          )
 74                  LEFT OUTER JOIN
 75                      ALFATS.ProposalAgreement
 76                          ON (
 77                              (
 78                                  MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
 79                              )
 80                              OR (
 81                                  ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
 82                              )
 83                          )
 84                  WHERE
 85                      (
 86                          (
 87                              (
 88                                  (
 89                                      MiscellaneousInformation.ownerDiscriminator = N'AGR'
 90                                  )
 91                                  OR (
 92                                      MiscellaneousInformation.ownerDiscriminator = N'SCH'
 93                                  )
 94                              )
 95                              AND (
 96                                  NOT (EXISTS (SELECT
 97                                      1
 98                                  FROM
 99                                      ALFATS.MiscellaneousInformation miscInfoInner
100                                  WHERE
101                                      ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
102                                      AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
103                                      AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
104                              )
105                          )                        AND (
106                              MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
107                          )
108                      ) )
109  WHERE  OWNERDISCRIMINATOR = 'AGR'
110  AND agreementnumber IS not NULL
111  ;

56011 rows selected.

Elapsed: 00:00:23.06

Execution Plan
----------------------------------------------------------
Plan hash value: 794275964

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                              |   671 |   370K|   179K  (1)| 00:00:08 |
|*  1 |  FILTER                                   |                              |       |       |            |          |
|   2 |   MERGE JOIN OUTER                        |                              |   671 |   370K|   179K  (1)| 00:00:08 |
|   3 |    MERGE JOIN OUTER                       |                              |   335 |   173K|   177K  (1)| 00:00:07 |
|   4 |     NESTED LOOPS OUTER                    |                              |   112 | 55440 |   176K  (1)| 00:00:07 |
|   5 |      NESTED LOOPS OUTER                   |                              |   112 | 51296 |   176K  (1)| 00:00:07 |
|   6 |       NESTED LOOPS OUTER                  |                              |   112 | 42448 |   176K  (1)| 00:00:07 |
|   7 |        NESTED LOOPS OUTER                 |                              |   112 | 35280 |   176K  (1)| 00:00:07 |
|   8 |         NESTED LOOPS ANTI                 |                              |   112 | 23072 |   176K  (1)| 00:00:07 |
|*  9 |          TABLE ACCESS FULL                | MISCELLANEOUSINFORMATION     | 11178 |  1299K|   176K  (1)| 00:00:07 |
|* 10 |          INDEX RANGE SCAN                 | MISCELLANEOUSINFORMATION_L2  |    13M|  1132M|     0   (0)| 00:00:01 |
|  11 |         TABLE ACCESS BY INDEX ROWID       | PROPOSALSCHEDULE             |     1 |   109 |     1   (0)| 00:00:01 |
|* 12 |          INDEX UNIQUE SCAN                | PROPOSALSCHEDULE_N2          |     1 |       |     0   (0)| 00:00:01 |
|  13 |        TABLE ACCESS BY INDEX ROWID BATCHED| MISCELLANEOUSINFOLISTVALUE   |     1 |    64 |     1   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN                  | MISCELLANEOUSINFOLISTVALUE_1 |     1 |       |     0   (0)| 00:00:01 |
|  15 |       TABLE ACCESS BY INDEX ROWID         | SCHEDULEENTITY               |     1 |    79 |     1   (0)| 00:00:01 |
|* 16 |        INDEX UNIQUE SCAN                  | SCHEDULEENTITY_N2            |     1 |       |     0   (0)| 00:00:01 |
|* 17 |      TABLE ACCESS BY INDEX ROWID          | SCHEDULE                     |     1 |    37 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN                   | SCHEDULE_PK                  |     1 |       |     0   (0)| 00:00:01 |
|  19 |     BUFFER SORT                           |                              |     3 |   105 |   177K  (1)| 00:00:07 |
|  20 |      VIEW                                 | VW_LAT_B4E6951E              |     3 |   105 |     9   (0)| 00:00:01 |
|  21 |       VIEW                                | VW_ORE_A774FCAE              |     3 |   105 |     9   (0)| 00:00:01 |
|  22 |        UNION-ALL                          |                              |       |       |            |          |
|  23 |         TABLE ACCESS BY INDEX ROWID       | AGREEMENT                    |     1 |   104 |     3   (0)| 00:00:01 |
|* 24 |          INDEX UNIQUE SCAN                | AGREEMENT_N2                 |     1 |       |     2   (0)| 00:00:01 |
|* 25 |         TABLE ACCESS BY INDEX ROWID       | AGREEMENT                    |     1 |   104 |     3   (0)| 00:00:01 |
|* 26 |          INDEX UNIQUE SCAN                | AGREEMENT_NK                 |     1 |       |     2   (0)| 00:00:01 |
|* 27 |         TABLE ACCESS BY INDEX ROWID       | AGREEMENT                    |     1 |   104 |     3   (0)| 00:00:01 |
|* 28 |          INDEX UNIQUE SCAN                | AGREEMENT_NK                 |     1 |       |     2   (0)| 00:00:01 |
|  29 |    BUFFER SORT                            |                              |     2 |    70 |   179K  (1)| 00:00:08 |
|  30 |     VIEW                                  | VW_LAT_B4E6951E              |     2 |    70 |     5   (0)| 00:00:01 |
|  31 |      VIEW                                 | VW_ORE_E1C15686              |     2 |    70 |     5   (0)| 00:00:01 |
|  32 |       UNION-ALL                           |                              |       |       |            |          |
|  33 |        TABLE ACCESS BY INDEX ROWID        | PROPOSALAGREEMENT            |     1 |   104 |     3   (0)| 00:00:01 |
|* 34 |         INDEX UNIQUE SCAN                 | PROPOSALAGREEMENT_N2         |     1 |       |     2   (0)| 00:00:01 |
|* 35 |        TABLE ACCESS BY INDEX ROWID        | PROPOSALAGREEMENT            |     1 |   104 |     2   (0)| 00:00:01 |
|* 36 |         INDEX UNIQUE SCAN                 | PROPOSALAGREEMENT_NK         |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
   9 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
              MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
  10 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
              "MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
              "MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
  12 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
  14 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(+) AND
              "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
  16 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
  17 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
  18 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
  24 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
  25 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  26 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
  27 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
  28 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
       filter(LNNVL("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
  34 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
  35 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
  36 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
    1487478  consistent gets
     648378  physical reads
        220  redo size
   11574128  bytes sent via SQL*Net to client
      41682  bytes received via SQL*Net from client
       3736  SQL*Net roundtrips to/from client
     112022  sorts (memory)
          0  sorts (disk)
      56011  rows processed

Summary

Although I could not identify the bug which is producing this issue, I was able to fix the problem. As the provider was not able to change the query, I created a baseline for the original query and then manipulate it to use the plan of the query with the alter session.

Oracle 12.2 - Wrong output in query with NESTED LOOPS and ANTI JOIN

1 Answers1