IC89273: CHAR VALUES WITH MORE THAN ONE ZERO BYTE NOT FOUND BY IS NULL/ISNOT NULL CLAUSES WHEN INDEX PATH IS USED

APAR status

• Closed as program error.

Error description

• Usually CHAR values which are NULL have a zero byte on the first
  position followed by spaces.
  For instance a CHAR(4) value which is null is stored as "00 20
  20 20".
  
  It might happen that third party software writes "00 00 00 00"
  instead of "00 20 20 20".
  An index on such a column will create additional keys for values
  with more than one zero byte at the beginning.
  
  Here is an oncheck -pK:
  
  Index idx_cid on TBLspace dbnull:informix.tnull
  Level 0 Node 0x1 Prev 0 Next 0
  Key:    "<00><00><00><00>":
  Rowids:       106
  Key:    "<00><00><00> ":
  Rowids:       105
  Key:    "<00><00>  ":
  Rowids:       104
  Key:    "<00>   ":
  Rowids:       103
  Key:    "    ":
  Rowids:       102
  Key:    "1111":
  Rowids:       101
  
  A SELECT using the index finds only the row with key "<00>   ":
  
  select {+INDEX(tnull idx_cid)}
  hex(rowid) hexrowid,cnote from tnull where cid is null
  
  hexrowid   cnote
  
  0x00000103 cid is null
  
  
  A SELECT using a sequential scan finds all the rows where the
  value starts with "00":
  
  select {+FULL(tnull)}
  hex(rowid) hexrowid,cnote from tnull where cid is null
  
  
  hexrowid   cnote
  
  0x00000103 cid is null
  0x00000104 cid=0x0000
  0x00000105 cid=0x000000
  0x00000106 cid=0x00000000
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * 11.70.XCX                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-11.70.xC8                                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-11.70.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