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.

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.1.3.3, 10.10.1.1
    • None
    • SQL
    • Normal
    • Repro attached
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            rhillegas Richard N. Hillegas

            Dates

              Created:
              Updated:

              Slack

                Issue deployment