db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: enhancement for like functionality
Date Fri, 10 Sep 2004 19:23:17 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=windows-1252"
 http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
-----BEGIN PGP SIGNED MESSAGE----- <br>
Hash: SHA1 <br>
 <br>
+1. I think LIKE predicate is used heavily and having a compatible<br>
syntax with standards and other products is a good thing. Should the<br>
*escape *also allow a constant character expression? Probably not as<br>
important, but again to match standards while changing this would be
good.<br>
<br>
Satheesh<br>
<br>
myrnap wrote:<br>
<br>
| Hi,<br>
|<br>
| I'd like to request an enhancement to the current derby 'LIKE'<br>
| functionality.<br>
|<br>
| Currently, the like functionality only allows actual strings as the<br>
|  second operand. It would be nice if also simple expressions that<br>
| equate to a string could be allowed, specifically, a simple cast.<br>
|<br>
| The SQL Standard says: "&lt;like predicate&gt; uses the triadic
operator<br>
| LIKE (or the inverse, NOT LIKE), operating on three character<br>
| strings and returning a Boolean. LIKE determines whether or not a<br>
| character string ‘‘matches’’ a given ‘‘pattern’’ (also a character<br>
| string). The characters &lt;percent&gt; and &lt;underscore&gt; have
special<br>
| meaning when they occur in the pattern. The optional third argument<br>
| is a character string containing exactly one character, known as<br>
| the ‘‘escape character’’, for use when a &lt;percent&gt;,
&lt;underscore&gt;,<br>
| or the ‘‘escape character’’ itself is required in the pattern<br>
| without its special meaning."<br>
|<br>
| This looks as if this refers to an actual string, however, in<br>
| various comments it seems that character string expressions and<br>
| character strings are interpreted as one.<br>
|<br>
| I tried my little test case against DB2 and HSQL and both allow<br>
| this, whereas Derby gives an error. To be precise,DB2 accepts a<br>
| constant, a special register, a host variable, a scalar function<br>
| whose operands are any of the above, or an expression concatenating<br>
|  any of the above. HSQL also accepts columnnames of character<br>
| datatype columns.<br>
|<br>
| testcase: create table t1 (c1 varchar(255)) insert into t1 values<br>
| ('apache derby') select * from t1 where c1 like cast ('apache<br>
| derby' as varchar(255)) current result: ERROR 42884: No authorized<br>
| routine named 'LIKE' of type 'FUNCTION' having compatible arguments<br>
| was found<br>
|<br>
| Note, that the following sql returns the one row: select * from t1<br>
| where c1 like 'apache derby' select * from t1 where c1 = cast<br>
| ('apache derby' as varchar(255))<br>
|<br>
| The code to be modified: in<br>
| org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java:<br>
| -------- // pattern must be a string or a parameter if<br>
| (!(leftOperand instanceof CharConstantNode) &amp;&amp;<br>
| !(leftOperand.isParameterNode())) throw<br>
|
StandardException.newException(SQLState.LANG_DB2_FUNCTION_INCOMPATIBLE,<br>
|  "LIKE", "FUNCTION"); -------<br>
|<br>
| If this seems like a good thing - to change so it accepts scalar<br>
| expressions that equate to a string - then I could make it so...<br>
|<br>
| Thx, Myrna<br>
|<br>
|<br>
<br>
-----BEGIN PGP SIGNATURE----- <br>
Version: GnuPG v1.2.5 (MingW32) <br>
Comment: Using GnuPG with Thunderbird - <a class="moz-txt-link-freetext" href="http://enigmail.mozdev.org">http://enigmail.mozdev.org</a>
<br>
 <br>
iD8DBQFBQf8lENVNIY6DZ7ERAnmWAJwLMhwVY81yxqDE951o8mS4hA7OWACeKFeu <br>
chBDXxkoWy6L7XzQw04V8Cg= <br>
=Z0Vp <br>
-----END PGP SIGNATURE----- <br>
<br>
</body>
</html>

Mime
View raw message