IT04651: WRONG RESULTS RETURNED IF LEFT OUTER JOIN ANSI SQL SYNTAX USED WITH SUBQUERY FLATTENING

APAR status

• Closed as program error.

Error description

• Query using ANSI SQL left outer join was returning no rows
  found.  Yet if we export NO_SUBQF environment variable then 1
  row is returned.
  The change in behavior is observed after upgrading from
  11.50.FC5 to 12.10.FC4. The query used to return the expected
  results set back in 11.50.
  

Local fix

• Workaround is to avoid sub query flattening by setting
  NO_SUBQF=1 from the client application before running the query.
  Another workaround is specific to disabling sub query flattening
  for statements with (Not Exists) in them by setting the onconfig
  parameter
  SQL_FEAT_CTRL 0x00008000.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC1 through 12.10.xC4.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with NOT EXISTS subquery may return incorrect        *
  * results under the following conditions:                      *
  *  - undocumented onconfig parameter SQL_FEAT_CTRL is          *
  * explicitly set, and it does not contain 0x8000 bit           *
  *  - main query is in ANSI JOIN format                         *
  *  - a NOT EXISTS subquery in the where clause                 *
  *  - the NOT EXISTS subquery contains a single table, and is   *
  * correlated with join conditions with one or more main query  *
  * table                                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-12.10.xC5                                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-12.10.xC5
  

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