Icon View Incident Report

Serious Serious
Reported By: Michael Binette
Reported On: 10/22/2004
For: Version 4.13 Build 1
# 1884 Join Optimization Can Cause Invalid Join Ordering with Certain Join Expressions

If you open the following SQL and run it in dbsys, look at two rows. The first column is ta_internal_id. If you scan, you will notice that two records in the results has the value 162 for ta_internal_id. Now edit the SQL (go all the way to the bottom) and uncomment the NOJOINOPTIMIZE line and run it again. You will notice that only one record has 162 and the other has 163. This is the correct results.

At the same time, look at the second and third columns. The JOIN clause links to the transaction_amount table (ta_ columns) where bp.bp_internal_id = ta_linkto_bp. Without the NOJOINOPTIMIZE you will notice that ta_linkto_bp (2nd column) does not match bp.bp_internal_id (3rd column). With NOJOINOPTIMIZE this works as expected.

SELECT b1.bp_internal_id ,
b1.bp_linkto_pa ,
INTO MEMORY\bp_filter
FROM billing_parties b1 
INNER JOIN billing_parties b2_pa ON
b1.bp_linkto_pa = b2_pa.bp_linkto_pa
WHERE (b2_pa.bp_level = 0) AND
((b1.bp_type = 0) OR (b1.bp_type = 1) OR (b1.bp_type = 2)) AND
(b2_pa.bp_type = 0)
GROUP BY b1.bp_internal_id;

CREATE INDEX i_bp_filter ON MEMORY\bp_filter (bp_internal_id);

SELECT trans_internal_id ,
trans_linkto_bp ,
INTO MEMORY\trans_filter
FROM transactions
WHERE (trans_date >= '0001-01-01') AND (trans_date < '2004-11-01');
CREATE INDEX i_trans_filter ON MEMORY\trans_filter (trans_internal_id);

SELECT ta_internal_id,
SUM(ta_amount) AS total_balance ,
CAST('0001-01-01' AS DATE) AS last_sess_date ,
MAX(IF (trans_type = 1 THEN trans_date ELSE NULL )) AS last_payment_date ,
bp.bp_bill_last_print AS last_bill_date ,
bp.bp_insform_print AS last_insform_date ,
1 as age_group_id ,
0.0 as age_current ,
0.0 as age_30 ,
0.0 as age_60 ,
0.0 as age_90 ,
0.0 as age_120 ,
ta_linkto_trans ,
trans_linkto_th ,
trans_linkto_pr ,
trans_linkto_acc ,
pa.bp_internal_id AS pa_bp_internal_id ,
pa.bp_linkto_pa ,
pa.bp_last_name AS pa_last_name ,
pa.bp_first_name AS pa_first_name ,
pa.bp_middle_initial AS pa_middle_initial ,
bp.bp_internal_id ,
bp.bp_type ,
bp.bp_level ,
ref_internal_id AS group_id
FROM billing_parties pa 
INNER JOIN billing_parties bp ON
pa.bp_linkto_pa = bp.bp_linkto_pa AND pa.bp_type = 0 
bp.bp_internal_id = bp_filter.bp_internal_id 
INNER JOIN transactions ON
bp.bp_linkto_pa = trans_linkto_pa 
INNER JOIN MEMORY\trans_filter tf ON
trans_internal_id = tf.trans_internal_id 
LEFT OUTER JOIN transaction_amount ON
bp.bp_internal_id = ta_linkto_bp AND ta_linkto_trans = trans_internal_id 
LEFT OUTER JOIN insurance_facesheet ON
bp.bp_linkto_ins = ins_internal_id 
LEFT OUTER JOIN referring_source ON
pa.bp_linkto_ref = ref_internal_id
GROUP BY ta_internal_id,
age_group_id, group_id
ORDER BY group_name,
bp.bp_level NOCASE

Comments Comments and Workarounds
The problem was with this join expression:

LEFT OUTER JOIN transaction_amount ON bp.bp_internal_id = ta_linkto_bp AND ta_linkto_trans = trans_internal_id

You'll notice that the bp.bp_internal_id = ta_linkto_bp expression is not related to either the driver or target table involved in this particular join. What was happening was that DBISAM was reordering the joins so that this expression was no longer valid due to the billing_parties (bp) table being ordered *after* the transaction_amount table. The optimizer has been fixed to now take this into account and not reorder the joins when it would put any expression in the joins in a situation where it cannot be evaluated with the same results as before the join reordering.

Resolution Resolution
Fixed Problem on 10/26/2004 in version 4.14 build 1