Informix

Informix set DB_LOCALE to en_us.utf8, the matches '[a-z]' got the wrong results.
I tested the Informix version 11.50, 11.70, 12.10, 14.10, all had this BUG.

Below is the test case:

export DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enutf8 with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enutf8 with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.

----------
When I executed the above SQL at en_us.8859-1 DB_LOCALE DB, the result was correct. Below is the results:

export DB_LOCALE=en_us.8859-1
export CLIENT_LOCALE=en_us.8859-1
unset GL_USEGLU
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enusdb with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enusdb with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
3 Y user03

2 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

2 n user02
4 y user04

2 row(s) retrieved.


------------------------------
Philip Yeh

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

#Informix

I tried this example on Unbuntu with Version 14.10.FC8AEE and

I got the correct results with both environments set.

Here the Version with uft8 setting:

Here the output of the test:

export DB_LOCALE=en_us.utf8
++ export DB_LOCALE=en_us.utf8
++ DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
++ export CLIENT_LOCALE=en_us.utf8
++ CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
++ export GL_USEGLU=1
++ GL_USEGLU=1
dbaccess -e - <<EOF
drop database if exists enutf8;
create database enutf8 with log;
create table test1 (id int, tsflag char(3), name char(30));
insert into test1 values(1,'N','user01');
insert into test1 values(2,'n','user02');
insert into test1 values(3,'Y','user03');
insert into test1 values(4,'y','user04');
select * from test1 where tsflag matches '[A-Z]';
select * from test1 where tsflag matches '[a-z]';

EOF
++ dbaccess -e -
drop database if exists enutf8;
Database dropped.

create database enutf8 with log;
Database created.

create table test1 (id int, tsflag char(3), name char(30));
Table created.

insert into test1 values(1,'N','user01');
1 row(s) inserted.

insert into test1 values(2,'n','user02');
1 row(s) inserted.

insert into test1 values(3,'Y','user03');
1 row(s) inserted.

insert into test1 values(4,'y','user04');
1 row(s) inserted.

select * from test1 where tsflag matches '[A-Z]';

id tsflag name
1 N user01
3 Y user03

2 row(s) retrieved.

select * from test1 where tsflag matches '[a-z]';

id tsflag name
2 n user02
4 y user04

2 row(s) retrieved.

------------------------------
Gerd Kaluzinski
------------------------------

Original Message:
Sent: Wed September 21, 2022 07:46 AM
From: Philip Yeh
Subject: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

Informix set DB_LOCALE to en_us.utf8, the matches '[a-z]' got the wrong results.
I tested the Informix version 11.50, 11.70, 12.10, 14.10, all had this BUG.

Below is the test case:

export DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enutf8 with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enutf8 with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.

----------
When I executed the above SQL at en_us.8859-1 DB_LOCALE DB, the result was correct. Below is the results:

export DB_LOCALE=en_us.8859-1
export CLIENT_LOCALE=en_us.8859-1
unset GL_USEGLU
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enusdb with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enusdb with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
3 Y user03

2 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

2 n user02
4 y user04

2 row(s) retrieved.


------------------------------
Philip Yeh

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

#Informix

Dear Gerd,
Thanks for your reply and testing.

Below is my testing on informix 14.10.FC8DE version and got the wrong results again:

on1410:/tmp $ sh -x test1.sh
+ cat /opt/ids1410/etc/GLS-cr /opt/ids1410/etc/IIF-cr /opt/ids1410/etc/MSG-cr
INFORMIX LIBGLS LIBRARY Version 7.00.FC6
Copyright (C) 2001-2022 IBM Corporation, All rights reserved
IBM Informix Dynamic Server Version 14.10.FC8
Copyright (C) 2001-2022 IBM Corporation, All rights reserved
IBM Informix Dynamic Server Version 14.10.FC8
Copyright (C) 2001-2022 IBM Corporation, All rights reserved
+ uname -a
Linux cent76x64 3.10.0-1160.59.1.el7.x86_64 #1 SMP Wed Feb 23 16:47:03 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
+ oninit -version
Program Name: oninit
Build Version: 14.10.FC8DE
Build Number: N169
Build Host: njdc-lxibm01
Build OS: Linux 3.10.0-693.el7.x86_64
Build Date: Tue Apr 5 20:37:02 CDT 2022
Build Timestamp: 2022-04-05T18:04:21-05
GLS Version: glslib-7.00.FC6
+ export DB_LOCALE=en_us.utf8
+ DB_LOCALE=en_us.utf8
+ export CLIENT_LOCALE=en_us.utf8
+ CLIENT_LOCALE=en_us.utf8
+ export GL_USEGLU=1
+ GL_USEGLU=1
+ dbaccess -e -
drop database if exists enutf8;
Database dropped.


create database enutf8 with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.

Database closed.

What's the problem on my environment ?

------------------------------
Philip Yeh
------------------------------

Original Message:
Sent: Thu September 22, 2022 08:26 AM
From: Gerd Kaluzinski
Subject: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

I tried this example on Unbuntu with Version 14.10.FC8AEE and

I got the correct results with both environments set.

Here the Version with uft8 setting:

Here the output of the test:

export DB_LOCALE=en_us.utf8
++ export DB_LOCALE=en_us.utf8
++ DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
++ export CLIENT_LOCALE=en_us.utf8
++ CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
++ export GL_USEGLU=1
++ GL_USEGLU=1
dbaccess -e - <<EOF
drop database if exists enutf8;
create database enutf8 with log;
create table test1 (id int, tsflag char(3), name char(30));
insert into test1 values(1,'N','user01');
insert into test1 values(2,'n','user02');
insert into test1 values(3,'Y','user03');
insert into test1 values(4,'y','user04');
select * from test1 where tsflag matches '[A-Z]';
select * from test1 where tsflag matches '[a-z]';

EOF
++ dbaccess -e -
drop database if exists enutf8;
Database dropped.

create database enutf8 with log;
Database created.

create table test1 (id int, tsflag char(3), name char(30));
Table created.

insert into test1 values(1,'N','user01');
1 row(s) inserted.

insert into test1 values(2,'n','user02');
1 row(s) inserted.

insert into test1 values(3,'Y','user03');
1 row(s) inserted.

insert into test1 values(4,'y','user04');
1 row(s) inserted.

select * from test1 where tsflag matches '[A-Z]';

id tsflag name
1 N user01
3 Y user03

2 row(s) retrieved.

select * from test1 where tsflag matches '[a-z]';

id tsflag name
2 n user02
4 y user04

2 row(s) retrieved.

------------------------------
Gerd Kaluzinski

Original Message:
Sent: Wed September 21, 2022 07:46 AM
From: Philip Yeh
Subject: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

Informix set DB_LOCALE to en_us.utf8, the matches '[a-z]' got the wrong results.
I tested the Informix version 11.50, 11.70, 12.10, 14.10, all had this BUG.

Below is the test case:

export DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enutf8 with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enutf8 with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.

----------
When I executed the above SQL at en_us.8859-1 DB_LOCALE DB, the result was correct. Below is the results:

export DB_LOCALE=en_us.8859-1
export CLIENT_LOCALE=en_us.8859-1
unset GL_USEGLU
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enusdb with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enusdb with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
3 Y user03

2 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

2 n user02
4 y user04

2 row(s) retrieved.


------------------------------
Philip Yeh

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

#Informix

Philip

Follow by excursion without explanation...

Platform:

[ user@informix-01 ] ~ > uname -a
Linux informix-01 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 25 17:23:54 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

[ user@informix-01 ] ~ > onstat -
HCL Informix Dynamic Server Version 12.10.FC13XT -- On-Line (Prim) -- Up 47 days 20:31:03 -- 10539680 Kbytes

Environment: us.utf8

[ user@informix-01 ] ~ > env | egrep "DB_LOCALE|CLIENT_LOCALE|GL_USEGLU"
CLIENT_LOCALE=en_us.utf8
GL_USEGLU=1
DB_LOCALE=en_us.utf8

Test data: enutf8

[ user@informix-01 ] ~ > echo "SELECT * FROM test1;" | dbaccess enutf8
Database selected.

         id tsflag name 
          1 N      user01 
          2 n      user02 
          3 Y      user03 
          4 y      user04 

4 row(s) retrieved.
Database closed.

Conditional SELECT 1:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[A-Z]';" | dbaccess enutf8
Database selected.

         id tsflag name 
          1 N      user01 
          3 Y      user03 

2 row(s) retrieved.
Database closed.

Conditional SELECT 2:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[a-z]';" | dbaccess enutf8
Database selected.

         id tsflag name 
          2 n      user02 
          4 y      user04 

2 row(s) retrieved.
Database closed.

Environment: us.8859-1

[ user@informix-01 ] ~ > export DB_LOCALE=en_us.8859-1
[ user@informix-01 ] ~ > export CLIENT_LOCALE=en_us.8859-1
[ user@informix-01 ] ~ > unset GL_USEGLU
[ user@informix-01 ] ~ > env | egrep "DB_LOCALE|CLIENT_LOCALE|GL_USEGLU"
CLIENT_LOCALE=en_us.8859-1
DB_LOCALE=en_us.8859-1

Test data: enusdb

[ user@informix-01 ] ~ > echo "select * from test1;" | dbaccess enusdb
Database selected.

         id tsflag name 
          1 N      user01 
          2 n      user02 
          3 Y      user03 
          4 y      user04 

4 row(s) retrieved.
Database closed.

Conditional SELECT 1:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[A-Z]';" | dbaccess enusdb
Database selected.

         id tsflag name 
          1 N      user01 
          3 Y      user03 

2 row(s) retrieved.
Database closed.

Conditional SELECT 2:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[a-z]';" | dbaccess enusdb
Database selected.

         id tsflag name 
          2 n      user02
          4 y      user04 

2 row(s) retrieved.
Database closed.

It does exactly what it says on the tin. I cannot reproduce your test case.

------------------------------
Kirit Rana
------------------------------

Original Message:
Sent: Wed September 21, 2022 07:46 AM
From: Philip Yeh
Subject: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

Informix set DB_LOCALE to en_us.utf8, the matches '[a-z]' got the wrong results.
I tested the Informix version 11.50, 11.70, 12.10, 14.10, all had this BUG.

Below is the test case:

export DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enutf8 with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enutf8 with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.

----------
When I executed the above SQL at en_us.8859-1 DB_LOCALE DB, the result was correct. Below is the results:

export DB_LOCALE=en_us.8859-1
export CLIENT_LOCALE=en_us.8859-1
unset GL_USEGLU
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enusdb with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enusdb with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
3 Y user03

2 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

2 n user02
4 y user04

2 row(s) retrieved.


------------------------------
Philip Yeh

------------------------------
#Informix

Hello, it can depend on collation in codeset. Try for example Czech cs_cz.utf8 (in Czech characters a and A have the same weight in sorting, similar to other characters, and character Ď has the same weight as D)

select s from slovo where s matches '[a-Z]*'
gives you:
s

aaaaa
DZ_aa
bbbbb
Bbbbb
Aaaaa
áčďéí
Ďáblice
Drda

8 row(s) retrieved.

select s from slovo where s matches '[A-Z]*'
gives you:
s

DZ_aa
bbbbb
Bbbbb
Aaaaa
áčďéí
Ďáblice
Drda

7 row(s) retrieved.

SQL command
select s from slovo where s matches '[abcded...xyz]*' should give you result you expected.



------------------------------
Milan Rafaj
Infrastructure Architects and Specialists Team Leader
Kyndryl
+420737264248
------------------------------

Original Message:
Sent: Thu September 22, 2022 08:50 AM
From: Kirit Rana
Subject: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

Philip

Follow by excursion without explanation...

Platform:

[ user@informix-01 ] ~ > uname -a
Linux informix-01 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 25 17:23:54 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

[ user@informix-01 ] ~ > onstat -
HCL Informix Dynamic Server Version 12.10.FC13XT -- On-Line (Prim) -- Up 47 days 20:31:03 -- 10539680 Kbytes

Environment: us.utf8

[ user@informix-01 ] ~ > env | egrep "DB_LOCALE|CLIENT_LOCALE|GL_USEGLU"
CLIENT_LOCALE=en_us.utf8
GL_USEGLU=1
DB_LOCALE=en_us.utf8

Test data: enutf8

[ user@informix-01 ] ~ > echo "SELECT * FROM test1;" | dbaccess enutf8
Database selected.

         id tsflag name 
          1 N      user01 
          2 n      user02 
          3 Y      user03 
          4 y      user04 

4 row(s) retrieved.
Database closed.

Conditional SELECT 1:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[A-Z]';" | dbaccess enutf8
Database selected.

         id tsflag name 
          1 N      user01 
          3 Y      user03 

2 row(s) retrieved.
Database closed.

Conditional SELECT 2:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[a-z]';" | dbaccess enutf8
Database selected.

         id tsflag name 
          2 n      user02 
          4 y      user04 

2 row(s) retrieved.
Database closed.

Environment: us.8859-1

[ user@informix-01 ] ~ > export DB_LOCALE=en_us.8859-1
[ user@informix-01 ] ~ > export CLIENT_LOCALE=en_us.8859-1
[ user@informix-01 ] ~ > unset GL_USEGLU
[ user@informix-01 ] ~ > env | egrep "DB_LOCALE|CLIENT_LOCALE|GL_USEGLU"
CLIENT_LOCALE=en_us.8859-1
DB_LOCALE=en_us.8859-1

Test data: enusdb

[ user@informix-01 ] ~ > echo "select * from test1;" | dbaccess enusdb
Database selected.

         id tsflag name 
          1 N      user01 
          2 n      user02 
          3 Y      user03 
          4 y      user04 

4 row(s) retrieved.
Database closed.

Conditional SELECT 1:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[A-Z]';" | dbaccess enusdb
Database selected.

         id tsflag name 
          1 N      user01 
          3 Y      user03 

2 row(s) retrieved.
Database closed.

Conditional SELECT 2:

[ user@informix-01 ] ~ > echo "SELECT * FROM test1 WHERE tsflag MATCHES '[a-z]';" | dbaccess enusdb
Database selected.

         id tsflag name 
          2 n      user02
          4 y      user04 

2 row(s) retrieved.
Database closed.

It does exactly what it says on the tin. I cannot reproduce your test case.

------------------------------
Kirit Rana

Original Message:
Sent: Wed September 21, 2022 07:46 AM
From: Philip Yeh
Subject: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

Informix set DB_LOCALE to en_us.utf8, the matches '[a-z]' got the wrong results.
I tested the Informix version 11.50, 11.70, 12.10, 14.10, all had this BUG.

Below is the test case:

export DB_LOCALE=en_us.utf8
export CLIENT_LOCALE=en_us.utf8
export GL_USEGLU=1
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enutf8 with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enutf8 with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

1 N user01
2 n user02
3 Y user03
4 y user04

4 row(s) retrieved.

----------
When I executed the above SQL at en_us.8859-1 DB_LOCALE DB, the result was correct. Below is the results:

export DB_LOCALE=en_us.8859-1
export CLIENT_LOCALE=en_us.8859-1
unset GL_USEGLU
on14fc8:/tmp$ dbaccess -e - <<EOF
> create database enusdb with log;
> create table test1 (id int, tsflag char(3), name char(30));
> insert into test1 values(1,'N','user01');
> insert into test1 values(2,'n','user02');
> insert into test1 values(3,'Y','user03');
> insert into test1 values(4,'y','user04');
> select * from test1 where tsflag matches '[A-Z]';
> select * from test1 where tsflag matches '[a-z]';
> EOF
create database enusdb with log;
Database created.


create table test1 (id int, tsflag char(3), name char(30));
Table created.


insert into test1 values(1,'N','user01');
1 row(s) inserted.


insert into test1 values(2,'n','user02');
1 row(s) inserted.


insert into test1 values(3,'Y','user03');
1 row(s) inserted.


insert into test1 values(4,'y','user04');
1 row(s) inserted.


select * from test1 where tsflag matches '[A-Z]';

id tsflag name

1 N user01
3 Y user03

2 row(s) retrieved.


select * from test1 where tsflag matches '[a-z]';

id tsflag name

2 n user02
4 y user04

2 row(s) retrieved.


------------------------------
Philip Yeh

------------------------------
#Informix