Return-Path: 
 <derby-dev-return-97162-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
X-Original-To: apmail-db-derby-dev-archive@www.apache.org
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id DE42BD560
	for <apmail-db-derby-dev-archive@www.apache.org>;
 Mon, 23 Jul 2012 13:02:36 +0000 (UTC)
Received: (qmail 87991 invoked by uid 500); 23 Jul 2012 13:02:36 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 87942 invoked by uid 500); 23 Jul 2012 13:02:36 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 87815 invoked by uid 99); 23 Jul 2012 13:02:36 -0000
Received: from issues-vm.apache.org (HELO issues-vm) (140.211.11.160)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 23 Jul 2012 13:02:36 +0000
Received: from isssues-vm.apache.org (localhost [127.0.0.1])
	by issues-vm (Postfix) with ESMTP id ED474142856
	for <derby-dev@db.apache.org>; Mon, 23 Jul 2012 13:02:35 +0000 (UTC)
Date: Mon, 23 Jul 2012 13:02:35 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <1619640814.90810.1343048555975.JavaMail.jiratomcat@issues-vm>
Subject: [jira] [Created] (DERBY-5875) Derby returns wrong results when you
 set the ESCAPE character to NULL in a LIKE clause.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394

Rick Hillegas created DERBY-5875:
------------------------------------

             Summary: Derby returns wrong results when you set the ESCAPE character to NULL in a LIKE clause.
                 Key: DERBY-5875
                 URL: https://issues.apache.org/jira/browse/DERBY-5875
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.10.0.0
            Reporter: Rick Hillegas


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) )'; 


--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira