You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2012/07/23 15:02:35 UTC

[jira] [Created] (DERBY-5875) Derby returns wrong results when you set the ESCAPE character to NULL in a LIKE clause.

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

[jira] [Commented] (DERBY-5875) Derby returns wrong results when you set the ESCAPE character to NULL in a LIKE clause.

Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
    [ 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

[jira] [Updated] (DERBY-5875) Derby returns wrong results when you set the ESCAPE character to NULL in a LIKE clause.

Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-5875?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-5875:
--------------------------------------

    Labels: derby_triage10_10  (was: )
    
> 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
>              Labels: derby_triage10_10
>
> 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
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] [Commented] (DERBY-5875) Derby returns wrong results when you set the ESCAPE character to NULL in a LIKE clause.

Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-5875?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13420652#comment-13420652 ] 

Rick Hillegas commented on DERBY-5875:
--------------------------------------

Hi Knut. Yes, I agree. If the LIKE pattern is set to NULL, that is supposed to cause the whole LIKE clause to evaluate to NULL, not to raise an exception. This is required by the same rule in the SQL Standard: part 2, section 8.5 <like predicate>, general rule 3.a.ii. Thanks.
                
> 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