IT30687: ASSERT FAILURE / -874 : GENERAL EXCEPTION ERROR HAS OCCURRED IN THE OPTIMIZER WHEN EXISTS CLAUSE USED WITH TIMESERIES VTI TABLE

APAR status

• Closed as program error.

Error description

• The following query produces an Assertion Failure and is
  returning error -874 (General exception error has occurred in
  the optimizer):
  
  SELECT 1 AS TIME_COMPONENT  FROM    VT_VEE_RDNG_INTVL I
  
    WHERE CORP_CD_ESI_ID =     '<value>' AND
                  RDNG_TYPE = '<value>' AND
                  ENRGY_DIR = '<value>' AND
                  RDNG_DTMGMT > '2014-01-01 05:15:00'     AND
                 RDNG_DTMGMT BETWEEN '2019-05-15 05:15:00' AND
  '2019-08-15 05:00:00' AND
                 RDNG_FLG = <value> AND
      EXISTS (   SELECT 1  FROM     VALID_TENANT_DATA_WORK
                  WHERE BATCH_ID = <value> AND
                      PREM_H = <value> AND
                      CA_OPEN_D <=     I.RDNG_DTMGMT AND
                      CA_CLOSE_D > I.RDNG_DTMGMT);
  
  Same query without the EXISTS clause runs OK.
  
  The stack looks like:
  
  18:29:33  Stack for thread: 168 sqlexec
  
  base: 0x0000000047c64000
    len:   102400
     pc: 0x000000000142e819
    tos: 0x0000000047ef65c0
  state: running
     vp: 10
  
  0x000000000142e819 (oninit) afstack
  0x00000000014317f1 (oninit) afhandler
  0x0000000001431fd2 (oninit) affail_interface
  0x0000000001432561 (oninit) mt_ex_throw_sig
  0x000000000143b453 (oninit) afsig_handler
  0x00007fd3431b3680 (Linux) <signal frame>
  0x00000000007f3e59 (oninit) sqoptim
  0x0000000000a314ec (oninit) bldstructs
  0x0000000000a35ea5 (oninit) sqcmd
  0x0000000000a40c2a (oninit) sq_cmnd
  0x0000000000a40d84 (oninit) sq_prepare
  0x0000000000b0b641 (oninit) sqmain
  0x000000000155d1ab (oninit) spawn_thread
  0x0000000001420130 (oninit) th_init_initgls
  0x0000000001465c48 (oninit) startup
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to Informix Server 12.10.xC14 or 14.10.xC3.           *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC14 and 14.10.xC3.
  

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