JR28676: QUERY WITH IN CLAUSE INSIDE WHERE CLAUSE GIVES WRONG OUTPUT.

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Query with IN clause inside WHERE clause gives wrong output.
  
  If we include some value twice in IN list, then output is
  correct. If same value appears in IN list only once, we get
  wrong output.
  
  When our customer does select from table using "IN" list in
  WHERE clause, we saw different output depending upon the values
  in IN list, which looks wrong.
  
  For eg.
  select count(*) from (
  SELECT   ID  from  VIEW_SCHEMA_5723.ENTITY WHERE ID IN
  (7681665,4326475,
  5782900,7383263,5398273,6016500,6903282,7591721,7585121,5402856,
  5407799,7617684,
  5406785,7383289,5410620,7617573,7617176,6355319,7640742,5398895,
  7407793,7644217,
  4340957,7383279,5412101,7368369,7617243,6555643,7617242,7383070,
  6588402,5320441,
  6316799,5398421,7640033,7383092,5320549,7640499,5708812,5407715,
  5569352,7649910,
  6414867,7175226,7374646,5320049,7383082,7383133,5952721,5397973,
  5398742,5751052,
  5398186,5397737,7368586,5320466,5397814,7640017,7245851,7617555,
  5408171,5320429,
  6592206,5319865,5999120,5806880,5449834,7027889,7617956,6349524,
  5319868,5398353,
  7590981,7662019,6903538,6074454,5319987,7383405,7665421,6026772,
  5408530,5398384,
  5398822,6943650,6007208,5651138,5485671,7525638,4270586,5866677,
  4271078,7050778,
  7525723,7648940,7597001,5401043,5398185,5660114,5485672,5320063,
  7368505,5398762,
  5412097,5569106,6414916,5412276,6277706,5320066,7368508,6151223,
  6865476,5398035,
  5398974,7640270,7838067,6095899,6864307,7617545,5460828,7220667,
  5319886,5319943,
  7617373,5320661,5410275,7588441,5397993,7011212,5407919,7617549,
  7787962,7586301,
  5897537,4291488,5923645,7640746,7382663,7159293,5410911,7640431,
  5320714,7370725,
  6107932,7601481,5398325,5320314,5569372,5398212,7370761,4326482,
  5871642,7093562,
  7661999,5320710,5407959,5387356,6023227,7585581,7617612,7593401,
  5398069,5408376,
  6039184,5319976,5398015,7617306,5397876,5398958,7604887,7239804,
  5614075,6592594,
  6903536,6319337,5398429,5782914,5398254,6163889,7596541,7665417,
  6862956,6117410,
  5412331,5398308,6119329,5485782,7585163,5406782,5397880,7617023,
  7105538,7640759,
  7640353,7640051,5319829,7681689,7102683,7639875,6628890,7640204,
  5319993,7617777,
  7617806,5665956,5398915,5320813,6096155,6628928,5398916,7656180,
  4269639,7598361,
  7617328,5569292,6077182,5786436,4284274,5469665,7589141,6628955,
  5397951,7617743,
  7640061,6089728,5408446,7368184,6358362,5494889,4263620,6107903,
  5398719,4339175,
  6628891,7368081,6358353,5410589,6625777,7592641,7650069,5998002,
  7382969,5485655,
  6865476,7640634,5371141,7639861,5565920,6012406,7590781,7644547,
  5660497,5398631,
  --6865476,6865476,                            <<<<<<<<<<<<<<<<
  --7640634,5371141,7639861,5565920,6012406,7590781,7644547,566049
  7,5398631,                                    <<<<<<<<<<<<<<<<
  
  --7640634,                                    <<<<<<<<<<<<<<<<
  --7640634,5371141,7639861,5565920,6012406,7590781,7644547,566049
  7,5398631,                                    <<<<<<<<<<<<<<<<
  --7640634,                                    <<<<<<<<<<<<<<<<
  
  4284289,6016508,6349209,5408590,5320224,5319831,5485771,7590821,
  7592201,5565816,
  7640357,7595701,5408594,5485796,5397777,5398564,7640242,7617646,
  7640344,5397887,
  7602977,5485563,7383025,7596601,7644371,5443107,7854700,7784551,
  5407871,7589301,
  6390486,7597781,5444906,7617149,5398454,5666215,7663618,7524603,
  5485749,7639981,
  7640546,5853450,7597921,5999144,5479251,7648936,7382833,7877324,
  7640652,7012931,
  6164360,6015084,7640381,6226533,5398692,5408648,7640339,6865449,
  7681690,5412081,
  5740271,5319672,5406827,5398657,5319903,7605376,6610088,6226540,
  5397906,7599441,
  7617177,7590241,5485658,5320482,5412084,5320614,5320265,5726575,
  7596501,5896132,
  7640368,7617778,5569041,5320227,7036312,7617140,5569110,7639917,
  6889886,5398614,
  6865372,7151666,7584741,6095863,7657305,7592701,5398268,5569356,
  6220343,6095857,
  6690856,5398422,4282075,7585207,5398529,7617312,5495249,5320516,
  5320008,7018647,
  7605188,7617446,5569077,7419242,6310229,6903375,7640089,6903382,
  7596101,6253170,
  
  6554445,5319874,5406265,7605350,7111656,5319737,7640153,7617109,
  5668400,6912191,
  --7605350,                                    <<<<<<<<<<<<<<<<
  
  7386722,7640618,7617204,7617293,5412032,6903296,5320348,6407092,
  7419959,5319882,
  5407993,7655460,5485779,5398470,7378753,7617568,7374653,7640626,
  5398730,5320251,
  5319782,5407990,6582853,6089726,5407775,5408600,6047438,6582833,
  5936979,7640683,
  5320338,5408083,7584681,7662180,6118021,7438384,5398194,7649075,
  5403416,5412198,
  7597881,5407580,5320010,7640588,5936281,5406803,6118058,7617111,
  7590021,5406278,
  7585961,5946310,5485612,7617311,5495565,5406718,5398600,7585215,
  5320128,5397851,
  5320511,6235479,7639683,5569317,7640281,5412000,6671304,7592141,
  6935832,5397898,
  6355408,7382355,5398583,7644526,5567446,5320800,5398666,7438559,
  6852462,7586281,
  5397991,5398697,5398972,7648881,7589521,5408092,5407700,7700816,
  7441882,5651134,
  5398574,7605354,7854680,5320386,5398275,7605380,7386283,6277610,
  7617441,6943574,
  5320525,7617641,7441873,7617651,5954197,5320200,7605435,7297671,
  7617262,5569026,
  7604668,5398867,5319586,7617417,7617021,5408454,7640725,6303302)
  ) t
  ORDER BY 1
  ;
  
  gives different output if we remove COMMENT characters (--) from
  start of commented line.
  
  The IN list literals are not pre-sorted in ascending or
  descending order.
  

Local fix

• db2set DB2_EXTENDED_IN2JOIN=BINSEARCH_OFF
  
  Recycle the instance is required after setting this variable.
  

Problem summary

• Users affected: DB2 V95 FP 0 user
  
  Problem Description: QUERY WITH IN CLAUSE INSIDE WHERE CLAUSE
  GIVES WRONG OUTPUT.
  

Problem conclusion

• First fixed in DB2 UDB Version 95, FixPak 1.
  

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

  DB2 UDB EXE WIN

• Fixed component ID

  5724E4901

Applicable component levels

• R950 PSY

     UP