Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results
Philip Yeh reported that on en_us.utf8 with GL_USEGLU=1, MATCHES with [a-z] or [A-Z] returned both cases, in versions 11.50 to 14.10, while 8859-1 was correct. Gerd Kaluzinski and Kirit Rana got correct results on 14.10.FC8AEE and 12.10.FC13XT. Milan Rafaj said it depends on locale collation. Philip still saw wrong results on 14.10.FC8DE; unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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.utf8export 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-1unset 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 - <<EOFdrop 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
------------------------------
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.utf8GL_USEGLU=1
DB_LOCALE=en_us.utf8Test 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-1Test 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
------------------------------
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
------------------------------
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