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/18 15:28:34 UTC
[jira] [Created] (DERBY-5862) There is no way to escape the special
_ and % characters in DatabaseMetaData search strings
Rick Hillegas created DERBY-5862:
------------------------------------
Summary: There is no way to escape the special _ and % characters in DatabaseMetaData search strings
Key: DERBY-5862
URL: https://issues.apache.org/jira/browse/DERBY-5862
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.10.0.0
Reporter: Rick Hillegas
And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a way to configure a search string escape character. See the discussion on this email thread: http://old.nabble.com/derby-getProcedureColumns-td34176144.html#a34176144
--
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-5862) There is no way to escape the
special _ and % characters in DatabaseMetaData search strings
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13420615#comment-13420615 ]
Rick Hillegas commented on DERBY-5862:
--------------------------------------
Thanks for running that experiment, Knut. That useful Derby behavior appears to violate the Standard. If the escape character is set to null, then the LIKE clause is supposed to evaluate to Unknown (i.e., NULL) according to part 2, section 8.5 <like predicate>, general rule 3.a.ii.
> There is no way to escape the special _ and % characters in DatabaseMetaData search strings
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-5862
> URL: https://issues.apache.org/jira/browse/DERBY-5862
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a way to configure a search string escape character. See the discussion on this email thread: http://old.nabble.com/derby-getProcedureColumns-td34176144.html#a34176144
--
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-5862) There is no way to escape the
special _ and % characters in DatabaseMetaData search strings
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13419194#comment-13419194 ]
Rick Hillegas commented on DERBY-5862:
--------------------------------------
If we add a knob for configuring a search string escape character, then we could rewrite the metadata queries in metadata.properties to take advantage of this character. Everywhere where we currently say "LIKE ?" we could say instead "LIKE ? ESCAPE ?".
For backward compatibility, we would still need to handle the default case where there is no search string escape character. Unfortunately we can't hand an empty string to the ESCAPE clause. A query like the following raises an error because the SQL Standard specifies that the ESCAPE argument must be a 1 character string (see part 2 of the Standard, section 8.5 <like predicate>, general rule 3.b.i.1):
select tablename from sys.systables where tablename like ? escape '';
Note that the metadata queries run in a special "internal" mode. We could probably get away with allowing an empty string for the ESCAPE argument when we are running in that special "internal" mode.
A candidate solution for this issue would then be:
1) Add a knob so that applications can configure a search string escape character.
2) Add some special logic so that when we are running in "internal" mode, you can pass an empty string to the ESCAPE clause. The meaning of this special internal syntax would be the same as if you omitted the ESCAPE clause.
3) Change all of the "LIKE ?" clauses in metadata.properties to be "LIKE ? ESCAPE ?".
4) Change all of the code which invokes the metadata queries so that we stuff the second ? with the result of calling DatabaseMetaData.getSearchStringEscape().
This solution seems straightforward but tedious.
A variation of this approach (to eliminate steps 3 and 4) would be to modify "internal" mode (or create a special "metadata internal" mode). In this mode, the LIKE operator would always add an ESCAPE clause when DatabaseMetaData.getSearchStringEscape() evaluates to something other than the empty string.
In any event, we would want to write a battery of test cases to verify the escaping behavior of all of the DatabaseMetaData calls which take search strings as arguments.
Addressing this issue appears to me to be at least a 2 week mini-project.
> There is no way to escape the special _ and % characters in DatabaseMetaData search strings
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-5862
> URL: https://issues.apache.org/jira/browse/DERBY-5862
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a way to configure a search string escape character. See the discussion on this email thread: http://old.nabble.com/derby-getProcedureColumns-td34176144.html#a34176144
--
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-5862) There is no way to escape the special
_ and % characters in DatabaseMetaData search strings
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5862?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5862:
-----------------------------------
Issue & fix info: Release Note Needed
Labels: derby_triage10_10 (was: )
> There is no way to escape the special _ and % characters in DatabaseMetaData search strings
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-5862
> URL: https://issues.apache.org/jira/browse/DERBY-5862
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a way to configure a search string escape character. See the discussion on this email thread: http://old.nabble.com/derby-getProcedureColumns-td34176144.html#a34176144
--
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-5862) There is no way to escape the
special _ and % characters in DatabaseMetaData search strings
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13419601#comment-13419601 ]
Knut Anders Hatlen commented on DERBY-5862:
-------------------------------------------
We might be able to do this without adding more internal syntax, as it looks like we accept NULL in the escape clause if it's parameterized:
ij> prepare ps as 'select tablename from sys.systables where tablename like ''SYSD%'' escape ?';
ij> execute ps using 'values cast( null as char(1) )';
IJ WARNING: Autocommit may close using result set
TABLENAME
--------------------------------------------------------------------------------------------------------------------------------
SYSDEPENDS
SYSDUMMY1
2 rows selected
> There is no way to escape the special _ and % characters in DatabaseMetaData search strings
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-5862
> URL: https://issues.apache.org/jira/browse/DERBY-5862
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a way to configure a search string escape character. See the discussion on this email thread: http://old.nabble.com/derby-getProcedureColumns-td34176144.html#a34176144
--
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