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

Derby returns wrong results when you set the ESCAPE character to NULL in a LIKE clause.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.3.3, 10.10.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Wrong query result

      Description

      The LIKE clause is supposed to evaluate to NULL if the optional ESCAPE character is set to NULL. Instead, Derby treats this condition as equivalent to omitting the ESCAPE clause. This violates part 2 of the SQL Standard, section 8.5 <like predicate>, general rule 3.a.ii.

      Thanks to Knut for disclosing this problem via the following script:

      connect 'jdbc:derby:memory:db;create=true';

      prepare ps as 'select tablename from sys.systables where tablename like ''SYSD%'' escape ?';

      – should return no rows, but actually returns 2
      execute ps using 'values cast( null as char(1) )';

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                rhillegas Richard N. Hillegas
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: