PI79144: A SELECT WITH A PREDICATE THAT SPECIFIES A VALUE FOR A PARTITIONING COLUMN DECLARED AS DATE RETURNS INCORRECT

APAR status

• Closed as program error.

Error description

• In Big SQL version 4.2, for a table that is partitioned on a
  column declared as DATE or DATE STORED AS TIMESTAMP, queries
  that select specific values of that column may not return the
  expected rows.
  
  For example:
  
  db2 "create hadoop table p_date (i int) partitioned by (d date)"
  DB20000I  The SQL command completed successfully.
  
  db2 "insert into p_date values (1,'2017-01-31')"
  DB20000I  The SQL command completed successfully.
  
  db2 "select * from p_date where d = '2017-01-31'"
  
  I           D
  ----------- ----------
  
    0 record(s) selected.
  
  On Big SQL version 4.1, the above scenario returns the expected
  results.
  
  db2 "create hadoop table p_date (i int) partitioned by (d date)"
  DB20000I  The SQL command completed successfully.
  
  db2 "insert into p_date values (1,'2017-01-31')"
  DB20000I  The SQL command completed successfully.
  
  db2 "select * from p_date where d = '2017-01-31'"
  
  I           D
  ----------- ----------
            1 01/31/2017
  
    1 record(s) selected.
  

Local fix

• (1) Change the select to use a LIKE predicate instead of an
  equality (where column like 'value%')
  (2) Alter the table to force the use of the Java reader using :
  "ALTER TABLE p_date SET TBLPROPERTIES ('bigsql.io.engine' =
  'java')"
   ** Please remember to alter it back once the fix has been
  applied using:
   "ALTER TABLE p_date SET TBLPROPERTIES ('bigsql.io.engine' =
  'auto')"
  

Problem summary

• See error description.
  

Problem conclusion

• The problem is fixed in Version 4.3 and later fix packs.
  

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:

Modules/Macros

• n/a
  

Fix information

• Fixed component name

  INFO BIGINSIGHT

• Fixed component ID

  5725C0900

Applicable component levels

• R430 PSY

     UP