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

Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

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

• RB10 PSN

     UP