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

EXISTS may return the wrong value for sub-queries involving set operations

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.2.2.0
    • 10.3.1.4
    • SQL
    • None
    • Release Note Needed

    Description

      It seems like EXISTS on a SELECT returning zero rows returns false (as
      expected), but EXISTS on INTERSECT of two disjunct sets returns true,
      e.g EXISTS (values 1 intersect values 2).

      Yip Ng wrote on derby-dev:
      I believe its probably got to do with the EXISTS subquery transforming
      the original RCL to
      a TRUE boolean value for the INTERSECT. So during row comparison at
      execution time
      for INTERSECT processing since true == true(thus intersects), so it
      will always return 'BAD'. Likewise,

      select * from ( values 'OK' ) as T where exists (values 1 except values 2);

      This supposedly should return 'OK' but because of the boolean
      transformation mentioned
      above for EXISTS subquery, it will return no rows for EXCEPT
      processing.

      Attachments

        1. d2370_engine_v1.patch
          13 kB
          A B
        2. d2370_tests_v1.patch
          24 kB
          A B
        3. d2370_v1.stat
          0.8 kB
          A B
        4. d2370_writeup_v1.html
          24 kB
          A B
        5. releaseNote.html
          5 kB
          Richard N. Hillegas
        6. releaseNote.html
          5 kB
          A B
        7. repro.sql
          1 kB
          Dyre Tjeldvoll

        Issue Links

          Activity

            People

              army A B
              dyret Dyre Tjeldvoll
              Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: