IT34692: ISSUE CREATING A VIEW WITH CONVERSION OF INTEGER TO DATETIME WITH FRACTION.

APAR status

• Closed as program error.

Error description

• Having issues creating a view with conversion of integer to
  datetime with fraction.
  
  Steps to reproduce
  
  # create table
  create table table1(entered int8);
  insert into table1 values(1598967000426);
  insert into table1 values(1598970911059);
  
  Select entered, (DBINFO ('utc_to_datetime', (x0.entered /
  1000.0000000000000000 ) ) ::datetime year to fraction(3) +
  (mod(x0.entered , 1000 ) / 1000.0000000000000000 ) UNITS
  FRACTION )
  FROM table1 x0;
  
  # Create the view
  
  create view view1 (entered, exec_time) as
  Select entered, (DBINFO ('utc_to_datetime', (x0.entered /
  1000.0000000000000000 ) ) ::datetime year to fraction(3) +
  (mod(x0.entered , 1000 ) / 1000.0000000000000000 ) UNITS
  FRACTION )
  FROM table1 x0;
  
  select * from view1;
  
  # table output:
  entered (expression)
         1598967000426 2020-09-01 06:30:00.426
         1598970911059 2020-09-01 07:35:11.059
  
  #view output
  entered exec_time
         1598967000426 2020-09-01 06:30:00.000
         1598970911059 2020-09-01 07:35:11.000
  
  Note that the last 3 digits from the table ending in non-zero
  values, ie 426 and 059, while the last three digits from the
  view are '000'
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC15 and 14.10.xC6.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 12.10.xC15 (when available) or    *
  * 14.10.xC6 (when available).                                  *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC15 and 14.10.xC6.
  

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