MySQL Bugs: #1717: INNER JOIN on multiple derived tables(subqueries) not returning all rows

Bug #1717	INNER JOIN on multiple derived tables(subqueries) not returning all rows
Submitted:	30 Oct 2003 15:32	Modified:	5 Dec 2003 6:17
Reporter:	Daniel Wrenn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.0	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
I have tried multiple SQL work arounds and different variations of the same general SQL statement and have been unable to retrieve the correct results. I have tried with explicit JOIN statements and with the "=" sign as in Oracle.   I am trying to select data from 3 subqueries and then join the results on a common indexed key.

Individually, the SQL statements return the correct results, say three results sets a piece with the same key field values, but when I try to join the 3 on those key field values, only 1 result set will be returned when 3 result sets are expected. It seems no matter the number of results sets expected, only 1 is returned when using the INNER JOIN.  Using a LEFT JOIN returns nulls for certain fields like they don't exist in the subquery even though the subquery by itself evaluates to results. If I only join 2 of the subqueries I get what I expect as shown below, just without the 3rd table data obviously.  I will include the SQL for the query, but will add the table creation if need be.  There is GROUPING occurring in each statement, and then on the highest level select.

This same general SQL works as expected in Oracle with the same table structure and relationships, for whatever that is worth.

Individually the select statements return:

table A
id | committed
-----------
 1 |  5
 2 |  6
 3 |  7

table B
id | obligated
-----------
 1 |  2
 2 |  3
 3 |  4

table C
id | disbursed
-----------
 1 |  8
 2 |  9
 3 |  0

When I now use an INNER JOIN to join them together on id this is what I get:

id | committed | obligated | disbursed
--------------------------------------
 2 |     6     |    3      |    9

if I do a LEFT JOIN I get this:
id | committed | obligated | disbursed
--------------------------------------
 1 |    null   |    2      |    8
 2 |     6     |    3      |    9
 3 |    null   |    4      |    0

I expect this:
id | committed | obligated | disbursed
--------------------------------------
 1 |     5     |    2      |    8
 2 |     6     |    3      |    9
 3 |     7     |    4      |    0

How to repeat:
SELECT a.vendor_id, available, committed, obligated, disbursed
FROM
(SELECT obligation_plans.vendor_id, sum(commitment_execution.obligated) as obligated, sum(commitment_execution.disbursed) as disbursed FROM core.programs INNER JOIN execution.budget_programs ON programs.program_id = budget_programs.program_id INNER JOIN execution.obligation_programs ON budget_programs.budget_program_id = obligation_programs.budget_program_id INNER JOIN execution.obligation_plans ON obligation_programs.obligation_program_id = obligation_plans.obligation_program_id INNER JOIN execution.commitments ON obligation_plans.obligation_plan_id = commitments.obligation_plan_id INNER JOIN execution.commitment_execution ON commitments.commitment_id = commitment_execution.commitment_id WHERE programs.product_id = 1 GROUP BY obligation_plans.vendor_id) o
LEFT JOIN
(SELECT obligation_plans.vendor_id, sum(commitments.committed) AS committed FROM core.programs INNER JOIN execution.budget_programs ON programs.program_id = budget_programs.program_id INNER JOIN execution.obligation_programs ON budget_programs.budget_program_id = obligation_programs.budget_program_id INNER JOIN execution.obligation_plans ON obligation_programs.obligation_program_id = obligation_plans.obligation_program_id INNER JOIN execution.commitments ON obligation_plans.obligation_plan_id = commitments.obligation_plan_id WHERE programs.product_id = 1 GROUP BY obligation_plans.vendor_id) c
ON (o.vendor_id = c.vendor_id)
LEFT JOIN
(SELECT obligation_plans.vendor_id, sum(obligation_plans.available) AS available FROM core.programs INNER JOIN execution.budget_programs ON programs.program_id = budget_programs.program_id INNER JOIN execution.obligation_programs ON budget_programs.budget_program_id = obligation_programs.budget_program_id INNER JOIN execution.obligation_plans ON obligation_programs.obligation_program_id = obligation_plans.obligation_program_id WHERE programs.product_id = 1 GROUP BY obligation_plans.vendor_id) a
ON (o.vendor_id = a.vendor_id)

Just realized that it is grouping and summing the data in the table A so to update my scenerio the committed amount is 18.  This may then be a duplicate bug...

table A
id | committed
-----------
 1 |  5
 2 |  6
 3 |  7

table B
id | obligated
-----------
 1 |  2
 2 |  3
 3 |  4

table C
id | disbursed
-----------
 1 |  8
 2 |  9
 3 |  0

When I now use an INNER JOIN to join them together on id this is what I get:

id | committed | obligated | disbursed
--------------------------------------
 2 |     18    |    3      |    9

if I do a LEFT JOIN I get this:
id | committed | obligated | disbursed
--------------------------------------
 1 |    null   |    2      |    8
 2 |     18    |    3      |    9
 3 |    null   |    4      |    0

I expect this:
id | committed | obligated | disbursed
--------------------------------------
 1 |     5     |    2      |    8
 2 |     6     |    3      |    9
 3 |     7     |    4      |    0

I have this type of SQL all over the place, and am creating a MySQL library in addition to the Oracle library for our app.  I will need it in a couple weeks, do I have any hope of version 4.1.1 with a fix or should I implement a workaround?

You are very probably experiencing an issue that has already been corrected in 4.1.1.  (I have queries structured quite like yours which work fine.)

If you would like to test your specific scenario against 4.1.1 and do not have the capability to compile it yourself from the development source tree, submit a specific and complete test case and it will be tested against the latest sources to see if there are outstanding bugs.

A binary release of 4.1.1 should hopefully come fairly soon, but I cannot be more specific than that.

Thank you

Downloaded 4.1.1 and the bug I submitted is now fixed, so you can close this bug.

Alright, thank you for the update.