1. Derby
  2. DERBY-1620

SQL CASE statement returns ERROR 42X89 when including NULL as a return value


    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s:
    • Fix Version/s:
    • Component/s: SQL
    • Labels:
    • Environment:
      Windows XP


      This bug appears to be related to the DERBY-7 bug (NULLIF() function). When NULL is used during a CASE statement, Derby requires the NULL to be CAST to the appropriate type. This does not appear to meet the SQL 2003 Standard for the Case Expression (see attached Word document). See the attached Word document to view the Derby Community Discussion about this issue. See the attached .TXT to view the SYSINFO and to see an example of the steps to reproduce using IJ.

      Steps to Reproduce:

      ij>values case when 1=2 then 3 else NULL end;
      ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type.

      Current Workaround:
      ij>values case when 1=2 then 3 else cast(NULL as INT) end;

      1. sysinfo_and_example.txt
        4 kB
        John Peterson
      2. resultset.tmp
        98 kB
        John Peterson
      3. resultset.tmp
        102 kB
        John Peterson
      4. derbyall_report.txt
        27 kB
        John Peterson
      5. derby1620_test.patch
        14 kB
        John Peterson
      6. derby1620_test_v2.patch
        13 kB
        A B
      7. Derby_Community_Discussion.doc
        38 kB
        John Peterson
      8. ConditionalNode.diff
        13 kB
        John Peterson
      9. ConditionalNode.diff
        8 kB
        John Peterson
      10. ConditionalNode.diff
        13 kB
        John Peterson
      11. ConditionalNode.diff
        14 kB
        John Peterson

        Issue Links



            • Assignee:
              John Peterson
              John Peterson
            • Votes:
              2 Vote for this issue
              3 Start watching this issue


              • Created: