Informix

 View Only
  • 1.  Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

    Posted 3 days ago
    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

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


  • 2.  RE: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

    Posted 2 days ago

    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
    ------------------------------



  • 3.  RE: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

    Posted yesterday
    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
    ------------------------------



  • 4.  RE: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

    Posted 2 days ago
    Edited by Kirit Rana 2 days ago
    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
    ------------------------------



  • 5.  RE: Informix DB_LOCALE use utf8 matches '[a-z]' got wrong results

    Posted 2 days ago
    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
    ------------------------------