IT17086: SELECT WITH LIKE ON A NVARCHAR COLUMN IN A UTF8 DATABASE CAUSES WRONG RESULTS

APAR status

• Closed as program error.

Error description

• I have a utf8 database e.g. using en_US.utf8 and a base table
  with a nvarchar
  where an index is defined on it.
  
  export DB_LOCALE=en_US.utf8
  #export DB_LOCALE=de_DE.utf8
  
  dbaccess << EOF
  drop database test;
  create database test;
  CREATE TABLE testlike (  str NVARCHAR(10));
  CREATE INDEX xpktestlike ON testlike ( str );
  INSERT INTO testlike VALUES ('1234567890');
  EOF
  
  When I use the following query
  
  select str from testlike WHERE str like '123456789%';
  
  no rows are returned.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC1 through 12.10.xC7.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query may give incorrect results under the following       *
  * conditions:                                                  *
  *                                                              *
  *  - GL_USEGLU is set to 1 in environment                      *
  *  - UTF8 locale is used, e.g. en_US.utf8                      *
  *  - query contains a LIKE or MATCHES predicate on a column    *
  * that is defined as NVARCHAR(n)                               *
  *  - an index exists on the NVARCHAR column                    *
  *  - the pattern used in the LIKE/MATCHES predicate has (n-1)  *
  * characters followed by a wildcard ('%' for LIKE, '*' for     *
  * MATCHES)                                                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 12.10.xC8                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.10.xC8
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RC10 PSN

     UP