IT09065: SELECT QUERY AGAINST A REMOTE VIEW WHERE ANSI JOIN IS USED CAN RETURN A SYNTAX ERROR

APAR status

• Closed as program error.

Error description

• A syntax error can occur when a select query is ran against a
  synonym in server_b that points to a view on server_a, or when a
  query is ran on server_b against a view on server_a, in both
  cases these database server instances are in the same host.
  
  Syntax error only occurs if the view is useing ANSI join, for
  example:
  
  create view tstvw
      (
      fld1,
      fld4,
      fld2
      )
  as  select  a.fld1,
              b.fld4,
              b.fld2
        from  tab1 a
              left outer join tab2 b  on  a.fld1 = b.fld1
              left outer join tab3 c  on  a.fld3 = c.fld3;
  

Local fix

• A possible workaround is to replace the ANSI syntax with
  Informix syntax:
  
  create view tstvw
      (
      fld1,
      fld4,
      fld2
      )
  as  select  a.fld1,
              b.fld4,
              b.fld2
        from  tab1 a, outer tab2 b, outer tab3 c
       where  a.fld1 = b.fld1
         and  a.fld3 = c.fld3;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC1 through 12.10.xC5.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query involving a remote view may return syntax error      *
  * under the following conditions:                              *
  *   - the remote view is defined in ANSI OUTER JOIN syntax,    *
  * e.g. "create view v1(a,b) as select tab1.col1, tab2.col2     *
  * from tab1 left outer join tab2 on ...."                      *
  *   - the main query has a predicate in the where clause on a  *
  * view column that corresponds to a subservient table in the   *
  * view definition, and this predicate excludes NULL values,    *
  * e.g. select ... from remotedb@remoteserver:v1 where v1.b =   *
  * <constant>                                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 12.10.xC6                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.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

• RC10 PSN

     UP