MySQL REGEXP is case sensitive

[chungonn]

I found out that regexp when use in H2 mysql compatibility mode is
case sensitive whereas it is not in mysql. Is this the expected
behavior? I also realize that H2 mysql mode does not recognize the
RLIKE token is this also an expected behaviour?

[Thomas Mueller]

Hi,

> I found out that regexp when use in H2 mysql compatibility  mode is
> case sensitive whereas it is not in mysql. Is this the expected
> behavior?

Yes, it is expected. H2 is not 100% compatible to all other databases
- if it would, then it would be 100 times larger I guess. H2 just
tries to be compatible where possible and where it makes sense.

> I also realize that H2 mysql mode does not recognize the
> RLIKE token is this also an expected behaviour?

Yes it is expected.

Regards,
Thomas

[Chung Onn Cheong]

Hi Thomas,

Thanks for the quick response. Actually I am delighted to see the compatibility mode in H2. As i moved into MySQL all my testcases failed as it was using HSQL, now with some tweaking i can have my testcases and MySQL queries in sync to a certain extend.

Thanks for giving us H2.

Regards
chungonn

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

I just found out all comparisons in MySQL are case insensitive. H2
also supports this mode (using "set ignorecase true"), however you
need to set it before creating any tables. I will document this.
Example:

drop table test;
set ignorecase true;
create table test(name varchar(255));
insert into test values('Hello');
select * from test where name like 'hello';
select * from test where name = 'hello';
select * from test where name regexp 'hello';

Regards,
Thomas

[Thomas Mueller]

Hi,

I will document:

Text comparison in MySQL is case insensitive by default, while in H2
it is case sensitive (as in most other databases). H2 does support
case insensitive text comparison, but it needs to be set separately,
using SET IGNORECASE TRUE. This affects comparison using =, LIKE,
REGEXP.

Regards,
Thomas

[Maaartin G]

On Saturday, April 30, 2011 9:25:35 AM UTC+2, Thomas Mueller wrote:

Hi,

I just found out all comparisons in MySQL are case insensitive.

AFAIK, not always. It depends on the collation of the compared expressions. If the two collations differ, you get an error. Otherwise, it determines the case sensitivity and also the handling of Unicode, see

http://forums.mysql.com/read.php?103,187048,188748#msg-188748

In MySql and Firebird, each column has not only a charset, but also a collation. A comparison of two case insensitive columns should probably be always case insensitive...

Somewhere I saw a syntax like

... WHERE a = b COLLATE ...

Maybe it's MySql, maybe it's the standard, I don't know.

I don't like the global option "IGNORECASE" much. It's not as general as COLLATE and it adds state to the connection (which is a problem with connection pools). Actually, "=", LIKE, and REGEXP are ternary predicates (taking optional collation as the third argument).

[chungonn]

Hi Thomas,

Thanks for looking the matter and it will certainly help my reduce my
dev work in future as I use H2 for prototyping and switch to Mysql
after that. Prior to your last response I have painfully converted all
my queries to case insensitive via Mysql's UPPER function :(

May I suggest that case insensitivity be automatically set when MySQL
mode is being used in H2 instead of doing it explicitly this would
make H2's MySQL mode better matches the actual behavior of MySQL. I
am using hibernate 3.6 to generate all the tables, any tip how do I
add the SET IGNORECASE TRUE in hibernate?

Thanks in advance.

Regards
chungonn

On Apr 30, 3:31 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> May I suggest that case insensitivity be automatically set when MySQL
> mode is being used in H2 instead of doing it explicitly this would
> make H2's MySQL mode better matches the actual behavior of MySQL.

That's an option, but it would mean you can't enable the MySQL mode if
there are already tables in the database (see the SET IGNORECASE
documenation).

Instead, I will improve the documentation.

> am using hibernate 3.6 to generate all the tables, any tip how do I
> add the SET IGNORECASE TRUE in hibernate?

Yes, you could add it to the database URL:

jdbc:h2:~/test;MODE=MySQL;IGNORECASE=TRUE

Regards,
Thomas

[chungonn]

Hi Thomas,

Thanks for the tip.

Regards
chungonn

On May 4, 3:49 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[chungonn]

Hi Thomas,

I tried to using SET IGNORECASE=TRUE as per your instructions but
still I am not able to disable case sensitive matching with REGEXP or
LIKE.

Here's the jdbc url - jdbc:h2:~/test;MODE=MySQL;IGNORECASE=TRUE

The query below yields a FALSE result. Did I missed out anything?

SELECT 'HelloWorld' regexp 'HELLOWORLD';
FALSE

Regards
chungonn

On May 4, 3:49 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Dario Simone]

Hi chungonn

IGNORECASE=TRUE ist no applied to already existing tables, you have to
recreate your table for it to work

g
Dario

[Thomas Mueller]

eHi,

The setting makes all column in new tables 'varchar_ignorecase'. Example:

create table test(id int, name varchar);
insert into test values(1, 'HelloWorld');
select * from test where name regexp 'HELLOWORLD';

However, the setting doesn't apply to text literals itself, so 'Hello'
in a literal isn't 'ignorecase'. I guess you could say it's a bug... I
will check if there are any side effects if I change the behavior.

Regards,
Thomas

On Tue, May 31, 2011 at 5:54 PM, chungonn <chun...@gmail.com> wrote:

[Thomas Mueller]

Hi,

After running a few tests, I decided not to change the behavior
currently. Instead, I will document the current behavior:

String literals and parameters are still considered case sensitive
even if the IGNORECASE option is set.

The main reason is that the database would behave differently when
using parameters versus using literals.

Regards,
Thomas