Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4679

Several left outer joins causes unstable query with incorrect results

    Details

    • Type: Bug
    • Status: Closed
    • Priority: 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;

        Attachments

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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: