Description:
I am executing a big SQL Query with multiple joins and group by with sum. The Query works 90% of the cases and it randomly fails to give the correct result. We are unable to replicate whenever needed. When we execute the query several times it fails in one case. Tried to debug the code but unable to find any issue with the code. I believe there is some issue with the software.
Below is the query
SELECT NULL record_id,
Max( l_job_id ) job_id,
Max( p_cycle_id ) cycle_id,
Max( temp_data.eid ) eid,
Max( temp_data.eid_add1 ) eid_add1,
Max( temp_data.emp_name ) emp_name,
Max( temp_data.p_group_ee ) p_group_ee,
Max( temp_data.company_code ) company_code,
Max( temp_data.eg1 ) eg1,
Max( temp_data.eg2 ) eg2,
Max( temp_data.eg3 ) eg3,
Max( temp_data.eg4 ) eg4,
Max( temp_data.pgpdata ) pgpdata,
Max( temp_data.p_period ) p_period,
Max( temp_data.p_date ) p_date,
temp_data.cp_code cp_code,
round(Ifnull( SUM( temp_data.hours ), 0 ), 4) sum_hours,
round(Ifnull( SUM( temp_data.hours * temp_data.hpf), 0 ), 4) sum_h_prorated,
round(Ifnull( SUM( temp_data.amount * temp_data.f_sign ), 0 ), 4) sum_amount,
round(Ifnull( SUM( temp_data.amount * temp_data.f_sign * temp_data.afr), 0 ), 4) sum_amount_prorated,
round(Ifnull( SUM( temp_data.wage_base ),0), 4) wage_base,
round(Ifnull( SUM( temp_data.wage_base * temp_data.wbpf), 0), 4) sum_wb_prorated,
round(Ifnull( SUM( temp_data.cp_add1 ),0), 4) sum_cp_add1,
round(Ifnull( SUM( temp_data.cp_add1 * temp_data.capf), 0), 4) sum_cp_add1_prorated,
temp_data.new_eid new_eid,
temp_data.new_eid_add1 new_eid_add1,
temp_data.ccode ccode,
temp_data.cid cid,
IF(AVG(temp_data.hpf) <> MAX(temp_data.hpf), 'Multiple', temp_data.hpf) hpf,
IF(AVG(temp_data.afr) <> MAX(temp_data.afr), 'Multiple', temp_data.afr) afr,
IF(AVG(temp_data.wbpf) <> MAX(temp_data.wbpf), 'Multiple', temp_data.wbpf) wbpf,
IF(AVG(temp_data.capf) <> MAX(temp_data.capf), 'Multiple', temp_data.capf) capf,
round(if(SUM( temp_data.hours * temp_data.hpf) <> 0, SUM(temp_data.amount * f_sign * temp_data.afr)/SUM( temp_data.hours * temp_data.hpf), 0), 4) hourly_rate_prorated,
round(if(SUM( temp_data.wage_base * temp_data.wbpf) <> 0, (SUM(temp_data.amount * f_sign * temp_data.afr)/SUM( temp_data.wage_base * temp_data.wbpf)) * 100, 0), 4) wb_percent_prorated,
tax_auth tax_auth
FROM ( SELECT xdpl.eid,
xdpl.eid_add1,
xdel.emp_name,
xdel.p_group p_group_ee,
xdel.company_code,
xdel.emp_group1,
xdel.emp_group2,
xdel.emp_group3,
xdel.emp_group4,
xdpl.p_group pgpdata,
xdpl.p_period,
xdpl.p_date,
xdpl.p_component,
xdpl.tax_auth,
xdpl.pyc_add1,
xdpl.hours,
xmpl.hpf,
xdpl.amount,
xmpl.afr,
xdpl.wage_base,
xmpl.wbpf,
xmpl.capf,
xdpl.unit_of_measure,
xdpl.cp_add1,
xdpl.ccode,
xdpl.cycle_id,
xdpl.cid,
xmpl.cp_code,
IF( xmpl.f_sign = 'Y', -1, 1 ) f_sign,
-- Xdt_get_leg_trans_cp_code ( xdpl.p_component, xdpl.tax_auth, xdpl.pyc_add1, xdpl.ccode, xdpl.cid ) cp_code,
-- Xdt_get_leg_trans_flip_amt_sign( xdpl.p_component, xdpl.tax_auth, xdpl.pyc_add1, xdpl.ccode, xdpl.cid ) f_sign,
xme.new_eid,
xme.new_eid_add1
FROM XDT_TABLE_ONE xdpl
inner join XDT_TABLE_ONE xdel
ON xdpl.eid = xdel.eid AND
Ifnull( xdpl.eid_add1, '##1' ) = Ifnull( xdel.eid_add1, '##1' ) AND
xdpl.cid = xdel.cid AND
xdpl.cycle_id = xdel.cycle_id AND
xdpl.ccode = xdel.ccode
inner join XDT_TABLE_THREE xtplel
ON xtplel.eid = xdpl.eid AND
Ifnull( xtplel.eid_add1, '##1' ) = Ifnull( xdpl.eid_add1, '##1' ) AND
xtplel.cid = xdpl.cid AND
xtplel.cycle_id = xdpl.cycle_id AND
xtplel.ccode = xdpl.ccode
left outer join XDT_TABLE_FOUR xmpl
ON xmpl.p_component = xdpl.p_component AND
( xmpl.tax_auth = xdpl.tax_auth OR
( xmpl.tax_auth IS NULL AND
xdpl.tax_auth IS NULL ) ) AND
( xmpl.pyc_add1 = xdpl.pyc_add1 OR
( xmpl.pyc_add1 IS NULL AND
xdpl.pyc_add1 IS NULL ) ) AND
xmpl.ccode = xdpl.ccode AND
xmpl.cid = xdpl.cid INNER JOIN xdt_conf_cp_code xccc on xmpl.cp_code = xccc.cp_code AND xmpl.cid = xccc.cid
left outer join XDT_TABLE_FIVE xme
ON xme.leg_eid = xdpl.eid AND
Ifnull ( xme.leg_eid_add1, '##1' ) = Ifnull( xdpl.eid_add1, '##1' ) AND
xme.ccode = xdpl.ccode AND
xme.cid = xdpl.cid
WHERE xdpl.cid = p_cid AND
xdpl.cycle_id = p_cycle_id AND 1=1 ) temp_data
GROUP BY temp_data.cid,temp_data.ccode,IFNULL(temp_data.new_eid,temp_data.eid),IFNULL(temp_data.new_eid_add1,eid_add1),temp_data.cp_code;
How to repeat:
Unable to replicate this consistently.
Description: I am executing a big SQL Query with multiple joins and group by with sum. The Query works 90% of the cases and it randomly fails to give the correct result. We are unable to replicate whenever needed. When we execute the query several times it fails in one case. Tried to debug the code but unable to find any issue with the code. I believe there is some issue with the software. Below is the query SELECT NULL record_id, Max( l_job_id ) job_id, Max( p_cycle_id ) cycle_id, Max( temp_data.eid ) eid, Max( temp_data.eid_add1 ) eid_add1, Max( temp_data.emp_name ) emp_name, Max( temp_data.p_group_ee ) p_group_ee, Max( temp_data.company_code ) company_code, Max( temp_data.eg1 ) eg1, Max( temp_data.eg2 ) eg2, Max( temp_data.eg3 ) eg3, Max( temp_data.eg4 ) eg4, Max( temp_data.pgpdata ) pgpdata, Max( temp_data.p_period ) p_period, Max( temp_data.p_date ) p_date, temp_data.cp_code cp_code, round(Ifnull( SUM( temp_data.hours ), 0 ), 4) sum_hours, round(Ifnull( SUM( temp_data.hours * temp_data.hpf), 0 ), 4) sum_h_prorated, round(Ifnull( SUM( temp_data.amount * temp_data.f_sign ), 0 ), 4) sum_amount, round(Ifnull( SUM( temp_data.amount * temp_data.f_sign * temp_data.afr), 0 ), 4) sum_amount_prorated, round(Ifnull( SUM( temp_data.wage_base ),0), 4) wage_base, round(Ifnull( SUM( temp_data.wage_base * temp_data.wbpf), 0), 4) sum_wb_prorated, round(Ifnull( SUM( temp_data.cp_add1 ),0), 4) sum_cp_add1, round(Ifnull( SUM( temp_data.cp_add1 * temp_data.capf), 0), 4) sum_cp_add1_prorated, temp_data.new_eid new_eid, temp_data.new_eid_add1 new_eid_add1, temp_data.ccode ccode, temp_data.cid cid, IF(AVG(temp_data.hpf) <> MAX(temp_data.hpf), 'Multiple', temp_data.hpf) hpf, IF(AVG(temp_data.afr) <> MAX(temp_data.afr), 'Multiple', temp_data.afr) afr, IF(AVG(temp_data.wbpf) <> MAX(temp_data.wbpf), 'Multiple', temp_data.wbpf) wbpf, IF(AVG(temp_data.capf) <> MAX(temp_data.capf), 'Multiple', temp_data.capf) capf, round(if(SUM( temp_data.hours * temp_data.hpf) <> 0, SUM(temp_data.amount * f_sign * temp_data.afr)/SUM( temp_data.hours * temp_data.hpf), 0), 4) hourly_rate_prorated, round(if(SUM( temp_data.wage_base * temp_data.wbpf) <> 0, (SUM(temp_data.amount * f_sign * temp_data.afr)/SUM( temp_data.wage_base * temp_data.wbpf)) * 100, 0), 4) wb_percent_prorated, tax_auth tax_auth FROM ( SELECT xdpl.eid, xdpl.eid_add1, xdel.emp_name, xdel.p_group p_group_ee, xdel.company_code, xdel.emp_group1, xdel.emp_group2, xdel.emp_group3, xdel.emp_group4, xdpl.p_group pgpdata, xdpl.p_period, xdpl.p_date, xdpl.p_component, xdpl.tax_auth, xdpl.pyc_add1, xdpl.hours, xmpl.hpf, xdpl.amount, xmpl.afr, xdpl.wage_base, xmpl.wbpf, xmpl.capf, xdpl.unit_of_measure, xdpl.cp_add1, xdpl.ccode, xdpl.cycle_id, xdpl.cid, xmpl.cp_code, IF( xmpl.f_sign = 'Y', -1, 1 ) f_sign, -- Xdt_get_leg_trans_cp_code ( xdpl.p_component, xdpl.tax_auth, xdpl.pyc_add1, xdpl.ccode, xdpl.cid ) cp_code, -- Xdt_get_leg_trans_flip_amt_sign( xdpl.p_component, xdpl.tax_auth, xdpl.pyc_add1, xdpl.ccode, xdpl.cid ) f_sign, xme.new_eid, xme.new_eid_add1 FROM XDT_TABLE_ONE xdpl inner join XDT_TABLE_ONE xdel ON xdpl.eid = xdel.eid AND Ifnull( xdpl.eid_add1, '##1' ) = Ifnull( xdel.eid_add1, '##1' ) AND xdpl.cid = xdel.cid AND xdpl.cycle_id = xdel.cycle_id AND xdpl.ccode = xdel.ccode inner join XDT_TABLE_THREE xtplel ON xtplel.eid = xdpl.eid AND Ifnull( xtplel.eid_add1, '##1' ) = Ifnull( xdpl.eid_add1, '##1' ) AND xtplel.cid = xdpl.cid AND xtplel.cycle_id = xdpl.cycle_id AND xtplel.ccode = xdpl.ccode left outer join XDT_TABLE_FOUR xmpl ON xmpl.p_component = xdpl.p_component AND ( xmpl.tax_auth = xdpl.tax_auth OR ( xmpl.tax_auth IS NULL AND xdpl.tax_auth IS NULL ) ) AND ( xmpl.pyc_add1 = xdpl.pyc_add1 OR ( xmpl.pyc_add1 IS NULL AND xdpl.pyc_add1 IS NULL ) ) AND xmpl.ccode = xdpl.ccode AND xmpl.cid = xdpl.cid INNER JOIN xdt_conf_cp_code xccc on xmpl.cp_code = xccc.cp_code AND xmpl.cid = xccc.cid left outer join XDT_TABLE_FIVE xme ON xme.leg_eid = xdpl.eid AND Ifnull ( xme.leg_eid_add1, '##1' ) = Ifnull( xdpl.eid_add1, '##1' ) AND xme.ccode = xdpl.ccode AND xme.cid = xdpl.cid WHERE xdpl.cid = p_cid AND xdpl.cycle_id = p_cycle_id AND 1=1 ) temp_data GROUP BY temp_data.cid,temp_data.ccode,IFNULL(temp_data.new_eid,temp_data.eid),IFNULL(temp_data.new_eid_add1,eid_add1),temp_data.cp_code; How to repeat: Unable to replicate this consistently.