Description:
I am using InnoDB.
The only difference between the two statements below is the grouping on the highest level select. If I run each of the nested SQL statements independently, they run fine, but when I combine them as in STATEMENT 2 below, MySQL crashes every time. This happened the first time I ran this SQL after upgrading from 4.1.0 to 4.1.1 and everytime since. It did not crash 4.1.0. I did notice bug #2048, but could not determine if this was same bug because my GROUP BY actually works.
*** STATEMENT 2 ****The SQL below crashes MySQL****
SELECT a.vendor_type_id, allotment, available, committed, obligated, disbursed
FROM (SELECT vendor_type_id, sum(obligation_plans.available) AS available, 0 AS allotment
FROM core.programs INNER JOIN execution.budget_programs USING (program_id)
INNER JOIN execution.obligation_programs USING (budget_program_id)
INNER JOIN execution.obligation_plans USING (obligation_program_id)
INNER JOIN core.vendors USING (vendor_id)
WHERE product_id = 1 GROUP BY vendor_type_id) a
LEFT JOIN (SELECT vendor_type_id, sum(obligated) as obligated, sum(disbursed) as disbursed
FROM core.programs INNER JOIN execution.budget_programs USING (program_id)
INNER JOIN execution.obligation_programs USING (budget_program_id)
INNER JOIN execution.obligation_plans USING (obligation_program_id)
INNER JOIN execution.commitments USING (obligation_plan_id)
INNER JOIN execution.commitment_execution USING (commitment_id)
INNER JOIN core.vendors ON obligation_plans.vendor_id = vendors.vendor_id
WHERE product_id = 1 GROUP BY vendor_type_id) o ON a.vendor_type_id = o.vendor_type_id
LEFT JOIN (SELECT vendor_type_id, sum(committed) AS committed
FROM core.programs INNER JOIN execution.budget_programs USING (program_id)
INNER JOIN execution.obligation_programs USING (budget_program_id)
INNER JOIN execution.obligation_plans USING (obligation_program_id)
INNER JOIN core.vendors ON obligation_plans.vendor_id = vendors.vendor_id
INNER JOIN execution.commitments ON obligation_plans.obligation_plan_id = commitments.obligation_plan_id
WHERE product_id = 1 GROUP BY vendor_type_id) c ON a.vendor_type_id = c.vendor_type_id
*** STATEMENT 1 ****The SQL below works fine****
SELECT a.vendor_type_id, sum(allotment) as allotment, sum(available) as available, sum(committed) as committed, sum(obligated) as obligated, sum(disbursed) as disbursed
FROM (SELECT vendor_type_id, sum(obligation_plans.available) AS available, 0 AS allotment
FROM core.programs INNER JOIN execution.budget_programs USING (program_id)
INNER JOIN execution.obligation_programs USING (budget_program_id)
INNER JOIN execution.obligation_plans USING (obligation_program_id)
INNER JOIN core.vendors USING (vendor_id)
WHERE product_id = 1 GROUP BY vendor_type_id) a
LEFT JOIN (SELECT vendor_type_id, sum(committed) AS committed
FROM core.programs INNER JOIN execution.budget_programs USING (program_id)
INNER JOIN execution.obligation_programs USING (budget_program_id)
INNER JOIN execution.obligation_plans USING (obligation_program_id)
INNER JOIN core.vendors ON obligation_plans.vendor_id = vendors.vendor_id
INNER JOIN execution.commitments ON obligation_plans.obligation_plan_id = commitments.obligation_plan_id
WHERE product_id = 1 GROUP BY vendor_type_id) c ON a.vendor_type_id = c.vendor_type_id
LEFT JOIN (SELECT vendor_type_id, sum(obligated) as obligated, sum(disbursed) as disbursed
FROM core.programs INNER JOIN execution.budget_programs USING (program_id)
INNER JOIN execution.obligation_programs USING (budget_program_id)
INNER JOIN execution.obligation_plans USING (obligation_program_id)
INNER JOIN execution.commitments USING (obligation_plan_id)
INNER JOIN execution.commitment_execution USING (commitment_id)
INNER JOIN core.vendors ON obligation_plans.vendor_id = vendors.vendor_id
WHERE product_id = 1 GROUP BY vendor_type_id) o ON a.vendor_type_id = o.vendor_type_id
GROUP BY a.vendor_type_id ORDER BY a.vendor_type_id
How to repeat:
Try something similiar? If get time, will try to make a test set.
