Hi,
I'd like to request an enhancement to the current derby 'LIKE'
functionality.
Currently, the like functionality only allows actual strings as the
second operand.
It would be nice if also simple expressions that equate to a string
could be allowed, specifically, a simple cast.
The SQL Standard says:
"<like predicate> uses the triadic operator LIKE (or the inverse, NOT
LIKE), operating on three
character strings and returning a Boolean. LIKE determines whether or
not a character string
‘‘matches’’ a given ‘‘pattern’’ (also a character string). The
characters <percent> and <underscore> have special meaning when they
occur in the pattern. The optional third argument is a character string
containing exactly one character, known as the ‘‘escape character’’, for
use when a <percent>, <underscore>, or the ‘‘escape character’’ itself
is required in the pattern without its special meaning."
This looks as if this refers to an actual string, however, in various
comments it seems that character string expressions and character
strings are interpreted as one.
I tried my little test case against DB2 and HSQL and both allow this,
whereas Derby gives an error. To be precise,DB2 accepts a constant, a
special register, a host variable, a scalar function whose operands are
any of the above, or an expression concatenating any of the above.
HSQL also accepts columnnames of character datatype columns.
testcase:
create table t1 (c1 varchar(255))
insert into t1 values ('apache derby')
select * from t1 where c1 like cast ('apache derby' as varchar(255))
current result:
ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION'
having compatible arguments was found
Note, that the following sql returns the one row:
select * from t1 where c1 like 'apache derby'
select * from t1 where c1 = cast ('apache derby' as varchar(255))
The code to be modified:
in org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java:
--------
// pattern must be a string or a parameter
if (!(leftOperand instanceof CharConstantNode) &&
!(leftOperand.isParameterNode()))
throw
StandardException.newException(SQLState.LANG_DB2_FUNCTION_INCOMPATIBLE,
"LIKE", "FUNCTION");
-------
If this seems like a good thing - to change so it accepts scalar
expressions that equate to a string - then I could make it so...
Thx,
Myrna
|