db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrnap <my...@Golux.Com>
Subject enhancement for like functionality
Date Fri, 10 Sep 2004 18:09:33 GMT
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

Mime
View raw message