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

Wrong results with queries that use the JOIN ... ON syntax to join with views or other non-base table expressions.

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.1.4
    • 10.1.3.2, 10.2.2.1, 10.3.1.4
    • SQL
    • None
    • Release Note Needed

    Description

      I think both select statements in the attached repro script should return 1 row, but in fact the first statement returns 1 row and the second returns zero rows.

      The only difference between the two statements is that the columns in the UNION view are listed in a different order (bvw vs. bvw2).

      This seems like a bug to me; the order of the columns in the view definition shouldn't matter, should it?

      As Army noted on the derby-dev list, the fact that this reproduces with 10.0 means that it is not caused by some of the 10.2 optimizer changes. Something else is going wrong.

      Attachments

        1. TestDerby.java
          12 kB
          Katherine Marsden
        2. releaseNote.html
          4 kB
          A B
        3. releaseNote.html
          4 kB
          Myrna van Lunteren
        4. goodQuery.log
          7 kB
          Bryan Pendleton
        5. firstTryPatch.diff
          1 kB
          Bryan Pendleton
        6. DistinctTestNotes.txt
          3 kB
          Bryan Pendleton
        7. derby-2526.sql
          0.9 kB
          Bryan Pendleton
        8. d2526_v2.stat
          0.2 kB
          A B
        9. d2526_v2.patch
          9 kB
          A B
        10. d2526_v1.patch
          2 kB
          A B
        11. d2526_v1.html
          19 kB
          A B
        12. badQuery.log
          5 kB
          Bryan Pendleton

        Issue Links

          is part of

          Task - A task that needs to be done. DERBY-2034 Tracking of bugs that lead to incorrect results being stored or returned to the client

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-3023 Different result rows depending on the sequence of INNER JOIN and OUTER JOIN

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-4679 Several left outer joins causes unstable query with incorrect results

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-5933 SQL sorting error

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-4695 Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen.

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            People

              army A B
              bryanpendleton Bryan Pendleton
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: