IC66471: QUERY USING DYNAMIC HASH JOIN COULD RETURN WRONG RESULTS BECAUSE OF OTHER JOIN FILTERS

APAR status

• Closed as program error.

Error description

• A query using Dynamic Hash Join could return wrong results
  because of another join filters' condition fails with a space
  padding problem.
  
  It works if trim and matches condition are used.  For example:
  
  1) The following query returns no rows:
  
  
  QUERY: (OPTIMIZATION TIMESTAMP: 02-17-2010 15:28:19)
  
  ------
  
   SELECT a.emp_firstname , a.emp_midname , a.emp_surname ,
  b.prev_stn_cde
  ,  b.prev_desig_cd , b.prev_dept_cd , b.prev_loc_cd , c.crew_flg
   FROM
  pmm_employee a,pmt_tfr b,  pmm_designation c  WHERE
  (((((a.emp_no =?)
  AND (a.emp_no = b.emp_no)) AND (a.emp_desig_cd =
  c.emp_desig_cd)) AND
  (b.emp_no =? ))  AND (b.tfr_order_no =? ))
  
  
  
  Estimated Cost: 4
  
  Estimated # of Rows Returned: 2
  
  
  
    1) krclusr.a: INDEX PATH
  
  
  
      (1) Index Name: informix. 1203_7623
  
          Index Keys: emp_no   (Key-First)  (Serial, fragments:
  ALL)
          Lower Index Filter: krclusr.a.emp_no = 1837
  
          Index Key Filters:  (krclusr.a.emp_no = 1837 )
  
  
  
    2) krclusr.c: INDEX PATH
  
  
  
      (1) Index Name: informix. 1283_8281
  
          Index Keys: emp_desig_cd   (Serial, fragments: ALL)
  
          Lower Index Filter: krclusr.a.emp_desig_cd =
  
  krclusr.c.emp_desig_cd
  
  NESTED LOOP JOIN
  
  
  
    3) krclusr.b: INDEX PATH
  
  
  
      (1) Index Name: informix. 633_14540
  
          Index Keys: emp_no   (Key-First)  (Serial, fragments:
  ALL)
          Lower Index Filter: krclusr.b.emp_no = 1837
  
          Index Key Filters:  (krclusr.b.emp_no = 1837 )
  
  
  
  
  
  DYNAMIC HASH JOIN
  
      Dynamic Hash Filters: krclusr.a.emp_no = krclusr.b.emp_no
  
  
  
      Other Join Filters: krclusr.b.tfr_order_no = 'TFR/2007/345'
  
  
  
  
  
  Query statistics:
  
  -----------------
  
  
  
    Table map :
  
    ----------------------------
  
    Internal name     Table name
  
    ----------------------------
  
    t1                a
  
    t2                c
  
    t3                b
  
  
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t1     1          1         1          00:00.09   1
  
  
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t2     1          776       1          00:00.04   0
  
  
  
    type     rows_prod  est_rows  time       est_cost
  
    -------------------------------------------------
  
    nljoin   1          2         00:00.13   3
  
  
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t3     1          1         1          00:00.26   1
  
  
  
    type     rows_prod  est_rows  rows_bld  rows_prb  novrflo
  time
  est_cost
  
  
  
  ----------------------------------------------------------------
  --------
  
  ------
  
    hjoin    0          2         1         1         0
  00:00.39
  5
  
  
  2) This SQL statement with trim and matches returns the correct
  results:
  (Other join filter condition)
  
  A)
  SELECT {+ USE_HASH (b)} a.emp_firstname , a.emp_midname ,
  a.emp_surname , b.prev_stn_cde ,  b.prev_desig_cd ,
  b.prev_dept_cd , b.prev_loc_cd , c.crew_flg  FROM pmm_employee
  a,pmt_tfr b,  pmm_designation c  WHERE (((((a.emp_no ="1837" AND
  (a.emp_no = b.emp_no)) AND (a.emp_desig_cd = c.emp_desig_cd))
  AND (b.emp_no ="1837" ))  AND (trim(b.tfr_order_no) =
  "TFR/2007/345" )));
  ...
  DYNAMIC HASH JOIN
  
      Dynamic Hash Filters: informix.a.emp_no = informix.b.emp_no
  
  
  
      Other Join Filters: TRIM ( BOTH ' ' FROM
  informix.b.tfr_order_no ) =
  'TFR/2007/345'
  
  
  B)
  SELECT {+ USE_HASH (b)} a.emp_firstname , a.emp_midname ,
  a.emp_surname , b.prev_stn_cde ,  b.prev_desig_cd ,
  b.prev_dept_cd , b.prev_loc_cd , c.crew_flg  FROM pmm_employee
  a,pmt_tfr b,  pmm_designation c  WHERE (((((a.emp_no ="1837" AND
  (a.emp_no = b.emp_no)) AND (a.emp_desig_cd = c.emp_desig_cd))
  AND (b.emp_no ="1837" ))  AND (b.tfr_order_no matches
  "TFR/2007/345*" )));
  
  
  ...
  
  DYNAMIC HASH JOIN
  
      Dynamic Hash Filters: informix.a.emp_no = informix.b.emp_no
  
  
  
      Other Join Filters: informix.b.tfr_order_no MATCHES
  'TFR/2007/345*'
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of IDS 11.50.xC4 and IDS 11.50.xC5.                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When a table has a primary key, and all the columns of the   *
  * primary key have an equality predicate in a query, and the   *
  * table is used on the build side of a hash join, and the      *
  * table is accessed using an index that is NOT the primary key *
  * index, then potentially wrong result can occur for the       *
  * query.                                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC7 and above.                          *
  ****************************************************************
  

Problem conclusion

• The problem was first fixed in IDS 11.50.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

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP