IT34063: BOOLEAN PREDICATES RETURN WRONG RESULT WITH NULL INPUT

Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• The boolean predicates (IS TRUE, IS NOT FALSE etc.) are
  returning a NULL result with NULL input.  Boolean predicates
  should never return a NULL result.  When used in a where-clause,
  the problem may not be noticeable since a NULL (UNKNOWN) result
  in a where-clause returns 0 rows.  However, the problem is
  apparent if the boolean predicate is preceded with NOT in the
  where-clause:
  
       create table T (B boolean)
       DB20000I  The SQL command completed successfully.
  
       insert into T values (NULL)
       DB20000I  The SQL command completed successfully.
  
       select * from T where not(B is false)
  
       B
       ------
              <-- Wrong, one row should be returned
         0 record(s) selected.
  
  Also, the problem can be seen when the boolean predicate is
  referenced in a select-list:
  
       select B is true from T
  
       1
       ------
            - <-- Wrong, should be 0
  
         1 record(s) selected.
  

Local fix

• Use the corresponding built-in functions instead: SYSIBM.ISTRUE,
  SYSIBM.ISNOTTRUE etc.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 version 11.1 fix pack 6                       *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 version 11.1 fix pack 6
  

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:

  IT35060

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP