Portability issue for 'Like-clause' on non-string types ?

[Albert Kam]

Hello again Apache Derby,

I'm currently porting my little webapp from using mysql to apache derby.
One of the issue i'm having right now is the like clause being used for non-string types.
I tried the ij, issuing simple sql statement like :

ij> select sc.id from sms_command sc where sc.id like '%';
ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found.

But it works fine for the string type :
ij> select sc.id, sc.dbpool_name from sms_command sc where sc.dbpool_name like 'd%';
ID         |DBPOOL_NAME
---------------------------
6          |demo
14         |demo
21         |demo
23         |test

I tried describe the table, and here's the output :
ij> describe sms_command;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID                                |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
DBPOOL_NAME         |VARCHAR  |NULL|NULL|15    |NULL      |30        |NO
....

I'm quite sure that there's no point in this case to use like-clause for an integer typed column, but there are several cases that the like clause can be useful for integer typed column, like when trying to find out an records that contains such and such part of a number.

When using mysql, using like-clause on non-string types works in my proggie prior to porting it to derby.

Anyway, is there a configuration that can be use to be 'friendly' for using like-clause on non-string types ? Or perhaps anything else that i'm missing out from the docs ? :)

Regards,
Albert Kam

--
Do not pursue the past. Do not lose yourself in the future.
The past no longer is. The future has not yet come.
Looking deeply at life as it is in the very here and now,
the practitioner dwells in stability and freedom.
(Thich Nhat Hanh)

[Donald McLean-3]

I'm guessing that it wants a string type. You will probably have to
use a CAST. I'm not sure of the exact syntax, but I think that it
might look something like this:

select cast(sc.id as varchar) as sc_id from sms_command sc where sc_id like "%"

On Thu, Jun 26, 2008 at 12:42 AM, Albert Kam <moonblade.wolf@...> wrote:
>
> ij> select sc.id from sms_command sc where sc.id like '%';
> ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having
> compatible arguments was found.

Donald

[Dyre Tjeldvoll]

Albert Kam <moonblade.wolf@...> writes:

> Hello again Apache Derby,
>
> I'm currently porting my little webapp from using mysql to apache derby.
> One of the issue i'm having right now is the like clause being used for
> non-string types.

I have not checked the exact chapter and verse of the SQL standard, but
I'm pretty sure that LIKE for non-string data is a non-standard
extension. As described in the charter, Derby aims to be standard
compliant. MySQL on the other hand states fairly openly that they will
deviate from the standard when they think that is convenient. So
basically, porting from MySQL is going to require some effort, I'm
afraid.

> Anyway, is there a configuration that can be use to be 'friendly' for using
> like-clause on non-string types ? Or perhaps anything else that i'm missing
> out from the docs ? :)

Not 100% sure, but I don't think so.

--
dt

[Rick Hillegas-2]

Hi Albert,

As Dyre and Donald point out, the LIKE operator can only be applied to
strings. You can write your own casting function to turn your integers
into strings (or to extract other information which you may be encoding
in integers). Something like the following should work:

select * from t
where intToString( intCol ) like '34%'

Note that this kind of query fragment won't be optimizable. That is, the
optimizer won't be able to take advantage of useful indexes which you've
put on intCol.

Hope this helps,
-Rick

Albert Kam wrote:

> Hello again Apache Derby,
>
> I'm currently porting my little webapp from using mysql to apache derby.
> One of the issue i'm having right now is the like clause being used
> for non-string types.
> I tried the ij, issuing simple sql statement like :
>
> ij> select sc.id <http://sc.id> from sms_command sc where sc.id
> <http://sc.id> like '%';
> ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION'
> having compatible arguments was found.
>
> But it works fine for the string type :
> ij> select sc.id <http://sc.id>, sc.dbpool_name from sms_command sc
> where sc.dbpool_name like 'd%';
> ID         |DBPOOL_NAME
> ---------------------------
> 6          |demo
> 14         |demo
> 21         |demo
> 23         |test
>
> I tried describe the table, and here's the output :
> ij> describe sms_command;
> COLUMN_NAME        
> |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> ------------------------------------------------------------------------------
> ID                                |INTEGER  |0   |10  |10    
> |NULL      |NULL      |NO
> DBPOOL_NAME         |VARCHAR  |NULL|NULL|15    |NULL      |30        |NO
> ....
>
> I'm quite sure that there's no point in this case to use like-clause
> for an integer typed column, but there are several cases that the like
> clause can be useful for integer typed column, like when trying to
> find out an records that contains such and such part of a number.
>
> When using mysql, using like-clause on non-string types works in my
> proggie prior to porting it to derby.
>
> Anyway, is there a configuration that can be use to be 'friendly' for
> using like-clause on non-string types ? Or perhaps anything else that
> i'm missing out from the docs ? :)
>
> Regards,
> Albert Kam
>
> --
> Do not pursue the past. Do not lose yourself in the future.
> The past no longer is. The future has not yet come.
> Looking deeply at life as it is in the very here and now,
> the practitioner dwells in stability and freedom.
> (Thich Nhat Hanh)

[Geoff hendrey]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Hi Rick,

Is this optimizable?

LIKE '%' ||  ?  ||  '%'

Let me explain: I need to use prepared statements for security, but I also need the  ability to use wildcards characters  like '%'. I found that the above style, using "||" for concatenation worked, whereas this did not:

LIKE '%?%'

In the version above, the '?' simply got treated literally by the JDBC driver, and was not recognized as a query parameter, since it is really just part of the string itself. If the use of "||" slows down the query significantly, than that's a real problem, since there appears to be no other way to "inject" jdbc ? into the string.

-geoff

----- Original Message ----
From: Rick Hillegas <Richard.Hillegas@...>
To: Derby Discussion <derby-user@...>
Sent: Thursday, June 26, 2008 5:45:55 AM
Subject: Re: Portability issue for 'Like-clause' on non-string types ?

Hi Albert,

As Dyre and Donald point out, the LIKE operator can only be applied to
strings. You can write your own casting function to turn your integers
into strings (or to extract other information which you may be encoding
in integers). Something like the following should work:

select * from t
where intToString( intCol ) like '34%'

Note that this kind of query fragment won't be optimizable. That is, the
optimizer won't be able to take advantage of useful indexes which you've
put on intCol.

Hope this helps,
-Rick

Albert Kam wrote:

> Hello again Apache Derby,
>
> I'm currently porting my little webapp from using mysql to apache derby.
> One of the issue i'm having right now is the like clause being used
> for non-string types.
> I tried the ij, issuing simple sql statement like :
>
> ij> select sc.id <http://sc.id> from sms_command sc where sc.id
> <http://sc.id> like '%';
> ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION'
> having compatible arguments was found.
>
> But it works fine for the string type :
> ij> select sc.id <http://sc.id>, sc.dbpool_name from sms_command sc
> where sc.dbpool_name like 'd%';
> ID        |DBPOOL_NAME
> ---------------------------
> 6          |demo
> 14        |demo
> 21        |demo
> 23        |test
>
> I tried describe the table, and here's the output :
> ij> describe sms_command;
> COLUMN_NAME       
> |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> ------------------------------------------------------------------------------
> ID                                |INTEGER  |0  |10  |10   
> |NULL      |NULL      |NO
> DBPOOL_NAME        |VARCHAR  |NULL|NULL|15    |NULL      |30        |NO
> ....
>
> I'm quite sure that there's no point in this case to use like-clause
> for an integer typed column, but there are several cases that the like
> clause can be useful for integer typed column, like when trying to
> find out an records that contains such and such part of a number.
>
> When using mysql, using like-clause on non-string types works in my
> proggie prior to porting it to derby.
>
> Anyway, is there a configuration that can be use to be 'friendly' for
> using like-clause on non-string types ? Or perhaps anything else that
> i'm missing out from the docs ? :)
>
> Regards,
> Albert Kam
>
> --
> Do not pursue the past. Do not lose yourself in the future.
> The past no longer is. The future has not yet come.
> Looking deeply at life as it is in the very here and now,
> the practitioner dwells in stability and freedom.
> (Thich Nhat Hanh)

[Rick Hillegas-2]

Hi Geoff,

The Tuning Guide talks about LIKE processing in a section titled "LIKE
transformations": http://db.apache.org/derby/docs/10.4/tuning/ The
bottom line is that the trailing LIKE operand needs to begin with a
constant in order for the optimizer to transform the LIKE clause into
clauses that can be keyed. In the examples you give below, it looks like
your strings begin with variable text and the constant is embedded in
the middle. The optimizer will not be able to use an index in that case.

It may be that generated columns (DERBY-481) could help you out here
(once that feature is implemented). If the constant piece of your string
occurs in a regular position that is known at INSERT/UPDATE time, then
generated columns would let you write the following:

create table t
(
   originalString varchar( 50 ),
   tastyEmbeddedString generated always( extractTastyString(
originalString ) )
);
create index tastyIndex on t( tastyEmbeddedString );

...

select * from t
where tastyEmbeddedString = ?;

Hope this helps,
-Rick


Geoff hendrey wrote:

> Hi Rick,
>
> Is this optimizable?
>
> LIKE '%' ||  ?  ||  '%'
>
> Let me explain: I need to use prepared statements for security, but I
> also need the  ability to use wildcards characters  like '%'. I found
> that the above style, using "||" for concatenation worked, whereas
> this did not:
>
> LIKE '%?%'
>
> In the version above, the '?' simply got treated literally by the JDBC
> driver, and was not recognized as a query parameter, since it is
> really just part of the string itself. If the use of "||" slows down
> the query significantly, than that's a real problem, since there
> appears to be no other way to "inject" jdbc ? into the string.
>
> -geoff
>
>
>
> ----- Original Message ----
> From: Rick Hillegas <Richard.Hillegas@...>
> To: Derby Discussion <derby-user@...>
> Sent: Thursday, June 26, 2008 5:45:55 AM
> Subject: Re: Portability issue for 'Like-clause' on non-string types ?
>
> Hi Albert,
>
> As Dyre and Donald point out, the LIKE operator can only be applied to
> strings. You can write your own casting function to turn your integers
> into strings (or to extract other information which you may be encoding
> in integers). Something like the following should work:
>
> select * from t
> where intToString( intCol ) like '34%'
>
> Note that this kind of query fragment won't be optimizable. That is, the
> optimizer won't be able to take advantage of useful indexes which you've
> put on intCol.
>
> Hope this helps,
> -Rick
>
> Albert Kam wrote:
> > Hello again Apache Derby,
> >
> > I'm currently porting my little webapp from using mysql to apache derby.
> > One of the issue i'm having right now is the like clause being used
> > for non-string types.
> > I tried the ij, issuing simple sql statement like :
> >
> > ij> select sc.id <http://sc.id> <http://sc.id> from sms_command sc
> where sc.id
> > <http://sc.id> like '%';
> > ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION'
> > having compatible arguments was found.
> >
> > But it works fine for the string type :
> > ij> select sc.id <http://sc.id>, sc.dbpool_name from sms_command sc
> > where sc.dbpool_name like 'd%';
> > ID        |DBPOOL_NAME
> > ---------------------------
> > 6          |demo
> > 14        |demo
> > 21        |demo
> > 23        |test
> >
> > I tried describe the table, and here's the output :
> > ij> describe sms_command;
> > COLUMN_NAME      
> > |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> >
> ------------------------------------------------------------------------------
> > ID                                |INTEGER  |0  |10  |10  
> > |NULL      |NULL      |NO
> > DBPOOL_NAME        |VARCHAR  |NULL|NULL|15    |NULL      |30        |NO
> > ....
> >
> > I'm quite sure that there's no point in this case to use like-clause
> > for an integer typed column, but there are several cases that the like
> > clause can be useful for integer typed column, like when trying to
> > find out an records that contains such and such part of a number.
> >
> > When using mysql, using like-clause on non-string types works in my
> > proggie prior to porting it to derby.
> >
> > Anyway, is there a configuration that can be use to be 'friendly' for
> > using like-clause on non-string types ? Or perhaps anything else that
> > i'm missing out from the docs ? :)
> >
> > Regards,
> > Albert Kam
> >
> > --
> > Do not pursue the past. Do not lose yourself in the future.
> > The past no longer is. The future has not yet come.
> > Looking deeply at life as it is in the very here and now,
> > the practitioner dwells in stability and freedom.
> > (Thich Nhat Hanh)
>

[Albert Kam]

Hello Derby,

Thank you people .. for the helps.

I think i'll stick with the standard approach, which is cleaner and easier to migrate later.

Anyway i tried out the cast function, but it didnt work. =p
Later i checked in the reference pdf, there's a table of what can be casted into what,
and integer cant be casted to a string type.

Regards,
Albert Kam

--
Do not pursue the past. Do not lose yourself in the future.
The past no longer is. The future has not yet come.
Looking deeply at life as it is in the very here and now,
the practitioner dwells in stability and freedom.
(Thich Nhat Hanh)

[Knut Anders Hatlen]

Rick Hillegas <Richard.Hillegas@...> writes:

> Hi Albert,
>
> As Dyre and Donald point out, the LIKE operator can only be applied to
> strings. You can write your own casting function to turn your integers
> into strings (or to extract other information which you may be
> encoding in integers). Something like the following should work:
>
> select * from t
> where intToString( intCol ) like '34%'

We also have a built-in function called CHAR that does this:
http://db.apache.org/derby/docs/dev/ref/rrefbuiltchar.html

--
Knut Anders