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

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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 10.2.1.6
    • 10.3.1.4
    • SQL
    • None
    • Windows XP

    Description

      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;

      Attachments

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

        Issue Links

          Activity

            People

              john.peterson@pega.com John Peterson
              john.peterson@pega.com John Peterson
              Votes:
              2 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: