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

Incorrect handling of NULL in IN lists

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Bug behavior facts:
      Deviation from standard, Wrong query result

      Description

      I believe the following query should return null instead of false, as the IN list contains an unknown value (null), so one cannot say definitely that it doesn't contain 1:

      ij> values 1 in (cast(null as int), 2);
      1


      false

      1 row selected

      Per SQL:2003, part 2, 8.4 <in predicate>, syntax rules 2 and 5, the query is equivalent to these two queries:

      ij> values 1 in (values cast(null as int), 2);
      1


      NULL

      1 row selected
      ij> values 1 = any (values cast(null as int), 2);
      1


      NULL

      1 row selected

      They both return NULL, which I believe is the correct result for the first query too.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              knutanders Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: