[
https://issues.apache.org/jira/browse/DERBY-5875?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13420636#comment-13420636
]
Knut Anders Hatlen commented on DERBY-5875:
-------------------------------------------
If the LIKE pattern is parameterized, we raise an error if the ESCAPE character
is NULL:
ij> prepare ps as 'select tablename from sys.systables where tablename like ?
escape ?';
ij> execute ps using 'values (''SYSD%'', cast(null as char(1)))';
IJ WARNING: Autocommit may close using result set
TABLENAME
--------------------------------------------------------------------------------------------------------------------------------
ERROR 22501: An ESCAPE clause of NULL returns undefined results and is not
allowed. (errorCode = 30000)
I suppose the two cases should behave the same way.
> 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
