IGNORECASE=TRUE and comparing strings

[Max Sidnin]

Hello,

If H2 is started with option "IGNORECASE=TRUE" it works great when fields and strings are compared. But if two strings need to be compared it doesn't work as expected.

For example:

--------------------------------------------------------------------------------------

SELECT 1 WHERE 'Text' = 'text' OR 'Text' LIKE '%text%'

--------------------------------------------------------------------------------------

It will not return any result. Both conditions are false. The real scenario is related to CONCAT result.

Is it correct behavior or is it bug?

Thanks,

Max Sidnin

[Max Sidnin]

H2 version is 1.3.174

Full connection string is: ;LOG=1;MVCC=TRUE;PAGE_SIZE=16384;CACHE_TYPE=TQ;ALIAS_COLUMN_NAME=TRUE;IGNORECASE=TRUE;MAX_MEMORY_ROWS=100000;DB_CLOSE_DELAY=0;CIPHER=AES;CACHE_SIZE=40000;MAX_OPERATION_MEMORY=20000000;

[Thomas Mueller]

Hi,

The option "ignorecase=true" will convert all column that are created as "varchar" to "varchar_ignorecase". I think the problem is that text literals are still interpreted as varchar, not varchar_ignorecase, so that if you compare text literals with other text literals, this is still case sensitive. The following works as expected (1 result for each query):

create table test(id int, name varchar);

insert into test values(1, 'Text');

select * from test where name = 'text';

select * from test where name like '%text%';

I guess one solution would be to convert text literals to varchar_ignorecase when using the ignorecase option. I hope this doesn't break existing applications. 

> The real scenario is related to CONCAT result.

Could you describe the real scenario please?

Regards,

Thomas

On Wed, Oct 30, 2013 at 10:30 AM, Max Sidnin <msi...@gmail.com> wrote:

H2 version is 1.3.174

Full connection string is: ;LOG=1;MVCC=TRUE;PAGE_SIZE=16384;CACHE_TYPE=TQ;ALIAS_COLUMN_NAME=TRUE;IGNORECASE=TRUE;MAX_MEMORY_ROWS=100000;DB_CLOSE_DELAY=0;CIPHER=AES;CACHE_SIZE=40000;MAX_OPERATION_MEMORY=20000000;

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Max Sidnin]

Hi, sorry for the late response.

Could you describe the real scenario please?

For example we have next table with "ignorecase=true":

+-------------+----------------+
|   Prefix   |    Name    |
+-------------+----------------+
|     Mr.      |      Max     |
+-------------+----------------+
|     Mr.      |   Thomas   |
+-------------+----------------+
|     Mr.      |      Mike    |
+-------------+----------------+
|     Ms.     |      Mona   |
+-------------+----------------+

User can search by some kind of object presentation:
----------------------------------------------------------
SELECT * FROM table WHERE CONCAT(Prefix, ' ', Name) LIKE '%mr. m%'
----------------------------------------------------------
There are no results for search by concated values, but expected 2: Max, Mike.

Regards,
Max

[Thomas Mueller]

Hi,

Sorry for the delay. Maybe "set collation" could be used?

set collation en strength primary;

create table test(id int, name varchar);

insert into test values(1, 'Text');

select * from test where name = 'text';

select * from test where name like '%text%';

See also http://h2database.com/html/grammar.html#set_collation

Regards,

Thomas

--

[Max Sidnin]

Hi, 

COLLATION is a nice working solution. Thank you.

And thanks for already fixed issue https://groups.google.com/forum/#!topic/h2-database/LjcgA4Ier7Q

Regards,

Max