Re: enhancement for like functionality

[Satheesh Bandaram]

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1   +1. I think LIKE predicate is used heavily and having a compatible syntax with standards and other products is a good thing. Should the *escape *also allow a constant character _expression_? Probably not as important, but again to match standards while changing this would be good. Satheesh myrnap wrote: | 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 | | -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org   iD8DBQFBQf8lENVNIY6DZ7ERAnmWAJwLMhwVY81yxqDE951o8mS4hA7OWACeKFeu chBDXxkoWy6L7XzQw04V8Cg= =Z0Vp -----END PGP SIGNATURE-----