IT22207: SQL0901N "COLUMN NUMBER OUT OF RANGE" ERROR WHEN GROUP BY IN NULL PRODUCING SIDE OF OUTER JOIN

APAR status

• Closed as program error.

Error description

• DB2 query may produce SQL0901N -"column number out of range"
  error, when the following conditions are met:
  1) The query has Outer Join
  2) Null producing side of Outer Join has Group By
  3) There is IN predicate on column from Tuple preserving side.
  
  Example:
  drop table t1;
  create table t1(c1 int not null primary key);
  insert into t1 values(1),(2),(3);
  drop table t2;
  create table t2(c1 int not null, c2 int not null);
  
  select * from t1 left outer join (select sum(c2), c2 from t2
  group by c2)t on t1.c1=t.c2 and t1.c1 in (1,2);
  

Local fix

• 1) Rewrite the query to convert the IN predicate into OR or NOT
  IN predicate into AND
  2) Use db2set DB2COMPOPT=NO_GBPU (instance recycle is needed)
      or use optguideline:
  <query>
  /* <OPTGUIDELINES>
  <REGISTRY>
  <OPTION NAME='DB2_COMPOPT' VALUE='NO_GBPU'/>
  </REGISTRY>
  </OPTGUIDELINES>
  */
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 4
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IT22197

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels