Derby
  1. Derby
  2. DERBY-4679

Several left outer joins causes unstable query with incorrect results

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.5.3.2, 10.6.2.1, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Seen in production, Wrong query result

      Description

      select distinct
      t1.ITEM_ID,
      t1.STATE_ID,
      t1.JZ_DISCRIMINATOR
      from (
      select *
      from
      LM.ABSTRACT_INSTANCE z1
      where
      z1.JZ_DISCRIMINATOR = 238
      ) t1 left outer join
      LM.LAB_RESOURCE_OPERATINGSYSTEM j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID) left outer join
      LM.ABSTRACT_INSTANCE t2 on (j1.ITEM_ID = t2.ITEM_ID) left outer join
      LM.OPERATING_SYSTEM_SOFTWARE_INSTALL j2 on (t2.ITEM_ID = j2.JZ_PARENT_ID) left outer join
      LM.ABSTRACT_INSTANCE t3 on (j2.ITEM_ID = t3.ITEM_ID) left outer join
      LM.FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID) left outer join
      LM.FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID)
      where (
      t3.FAMILY_ITEM_ID in('_5VetVWTeEd-Q8aOqWJPEIQ') and
      (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and
      (t7.ROOT_ITEM_ID = '_5nN9mmTeEd-Q8aOqWJPEIQ') and
      (t1.VISIBILITY = 0)
      )

      The above query returns no results despite the fact that the database contains results that match the query.
      Slight modifications to the query that shouldn't change the outcome cause it to return the expected results. For example: changing
      "t3.FAMILY_ITEM_ID in('_5VetVWTeEd-Q8aOqWJPEIQ')"
      to
      "t3.FAMILY_ITEM_ID in('_5VetVWTeEd-Q8aOqWJPEIQ', 'blah')"
      or
      "t3.FAMILY_ITEM_ID = '_5VetVWTeEd-Q8aOqWJPEIQ'"

      or removing
      "(t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and "
      despite the fact that the results show that t5.ROOT_ITEM_ID is equal to the string above.

      Note that there is no error message associated with the incorrect query, it just returns zero rows.

      The query below doesn't use join statements and works as expected.

      select distinct
      t1.item_id,
      t1.state_id,
      t1.jz_discriminator
      from (
      select *
      from
      lm.abstract_instance z1
      where
      z1.jz_discriminator = 238
      ) t1,
      lm.lab_resource_operatingsystem j1,
      lm.abstract_instance t3,
      lm.operating_system_software_install j2,
      lm.abstract_instance t2,
      lm.family t5,
      lm.family t7
      where
      t1.item_id = j1.jz_parent_id and
      j1.item_id = t2.item_id and
      t2.item_id = j2.jz_parent_id and
      j2.item_id = t3.item_id and
      t2.family_item_id = t5.item_id and
      t1.family_item_id = t7.item_id and
      t3.family_item_id in ('_5VetVWTeEd-Q8aOqWJPEIQ') and
      t5.root_item_id = '_5ZDlwWTeEd-Q8aOqWJPEIQ' and
      t7.root_item_id = '_5nN9mmTeEd-Q8aOqWJPEIQ' and
      t1.visibility = 0;

      1. trace-in.log
        1 kB
        Dag H. Wanvik
      2. trace-exec-plan.log
        15 kB
        Dag H. Wanvik
      3. trace-equal.log
        3 kB
        Dag H. Wanvik
      4. show.diff
        5 kB
        Dag H. Wanvik
      5. sample.sql
        3 kB
        David Richards
      6. ins-after-preprocess.log
        244 kB
        Dag H. Wanvik
      7. in-after-preprocess.log
        242 kB
        Dag H. Wanvik
      8. Foo.java
        2 kB
        Dag H. Wanvik
      9. equal-after-preprocess.log
        189 kB
        Dag H. Wanvik
      10. drawing.txt
        7 kB
        Dag H. Wanvik
      11. drawing.txt
        7 kB
        Dag H. Wanvik
      12. derby-4679-followup.stat
        0.1 kB
        Dag H. Wanvik
      13. derby-4679-followup.diff
        4 kB
        Dag H. Wanvik
      14. derby-4679b.stat
        0.3 kB
        Dag H. Wanvik
      15. derby-4679b.diff
        12 kB
        Dag H. Wanvik
      16. derby-4679a.stat
        0.3 kB
        Dag H. Wanvik
      17. derby-4679a.diff
        12 kB
        Dag H. Wanvik
      18. derby-4679-2a.stat
        0.2 kB
        Dag H. Wanvik
      19. derby-4679-2a.diff
        14 kB
        Dag H. Wanvik
      20. db.sql
        4 kB
        David Richards

        Issue Links

          Activity

          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12511871 ] Default workflow, editable Closed status [ 12801988 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-5933 [ DERBY-5933 ]
          Kathey Marsden made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Assignee Kathey Marsden [ kmarsden ] Dag H. Wanvik [ dagw ]
          Fix Version/s 10.5.3.2 [ 12315436 ]
          Resolution Fixed [ 1 ]
          Kathey Marsden made changes -
          Assignee Dag H. Wanvik [ dagw ] Kathey Marsden [ kmarsden ]
          Kathey Marsden made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          Kathey Marsden made changes -
          Link This issue is required by DERBY-4994 [ DERBY-4994 ]
          Rick Hillegas made changes -
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Fix Version/s 10.7.1.0 [ 12314971 ]
          Knut Anders Hatlen made changes -
          Fix Version/s 10.6.2.1 [ 12315343 ]
          Fix Version/s 10.6.2.0 [ 12315342 ]
          Kathey Marsden made changes -
          Fix Version/s 10.6.2.0 [ 12315342 ]
          Fix Version/s 10.6.1.1 [ 12314973 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available, Repro attached] [Repro attached]
          David Richards made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Resolved [ 5 ]
          Dag H. Wanvik made changes -
          Fix Version/s 10.6.1.1 [ 12314973 ]
          Fix Version/s 10.7.0.0 [ 12314971 ]
          Dag H. Wanvik made changes -
          Attachment derby-4679-2a.diff [ 12448083 ]
          Attachment derby-4679-2a.stat [ 12448084 ]
          Dag H. Wanvik made changes -
          Attachment derby-4679-followup.diff [ 12447363 ]
          Attachment derby-4679-followup.stat [ 12447364 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-4695 [ DERBY-4695 ]
          Dag H. Wanvik made changes -
          Attachment derby-4679b.diff [ 12446415 ]
          Attachment derby-4679b.stat [ 12446416 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Repro attached] [Patch Available, Repro attached]
          Dag H. Wanvik made changes -
          Attachment derby-4679a.diff [ 12446387 ]
          Attachment derby-4679a.stat [ 12446388 ]
          Dag H. Wanvik made changes -
          Attachment drawing.txt [ 12446258 ]
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-2526 [ DERBY-2526 ]
          Dag H. Wanvik made changes -
          Attachment drawing.txt [ 12446174 ]
          Dag H. Wanvik made changes -
          Attachment show.diff [ 12446068 ]
          Attachment Foo.java [ 12446069 ]
          Dag H. Wanvik made changes -
          Attachment in-after-preprocess.log [ 12445843 ]
          Attachment ins-after-preprocess.log [ 12445844 ]
          Attachment equal-after-preprocess.log [ 12445845 ]
          Dag H. Wanvik made changes -
          Attachment trace-exec-plan.log [ 12445840 ]
          Attachment trace-equal.log [ 12445841 ]
          Attachment trace-in.log [ 12445842 ]
          Dag H. Wanvik made changes -
          Bug behavior facts [Deviation from standard, Wrong query result] [Deviation from standard, Seen in production, Wrong query result]
          Dag H. Wanvik made changes -
          Issue & fix info [Repro attached]
          Dag H. Wanvik made changes -
          Affects Version/s 10.6.1.0 [ 12313727 ]
          Affects Version/s 10.5.2.0 [ 12314116 ]
          Affects Version/s 10.5.1.1 [ 12313771 ]
          Affects Version/s 10.4.2.0 [ 12313345 ]
          Affects Version/s 10.4.1.3 [ 12313111 ]
          Affects Version/s 10.3.3.0 [ 12313142 ]
          Affects Version/s 10.3.2.1 [ 12312876 ]
          Affects Version/s 10.3.1.4 [ 12312590 ]
          Affects Version/s 10.2.2.0 [ 12312027 ]
          Affects Version/s 10.2.1.6 [ 11187 ]
          Affects Version/s 10.1.3.1 [ 12311953 ]
          Affects Version/s 10.1.2.1 [ 12310615 ]
          Affects Version/s 10.1.1.0 [ 10993 ]
          Affects Version/s 10.0.2.1 [ 10991 ]
          David Richards made changes -
          Attachment db.sql [ 12445654 ]
          Attachment sample.sql [ 12445655 ]
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Bug behavior facts [Deviation from standard] [Deviation from standard, Wrong query result]
          David Richards created issue -

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              David Richards
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development