Description:
When performing an outer join, sometimes the query results will be missing rows that should belong in the resultset. Most of the time I don't get this problem, but for some reason it always happens with this query for my inventory database. This problem has existed since v4.0 (never tested on 3.x).
>>>SELECT SQL_NO_CACHE b.inventory_id as inventory_id,
b.manufacturer as manu, b.sku as sku, b.mmspct as mmspct, b.stype as stype, LENGTH(b.memo)=0 as hasNoMemo,
sum(IF(a.cost>0,a.cost,a.price)*a.quantity)/sum(a.quantity) as cost,
sum(IF(a.cost>0,a.cost,a.price)*a.quantity) as tcost,
sum(a.price*a.quantity)/sum(a.quantity) as price,
sum(a.price*a.quantity) as tprice,
sum(a.quantity) as quantity
FROM invOrderItems as a RIGHT JOIN invProducts as b USING(inventory_id)
WHERE b.active='Y' AND (a.date > '2005-06-29 00:00:00' OR a.date IS NULL)
GROUP BY b.inventory_id ORDER BY manu, sku
....
| 1636 | VTech | VT5888 (VT5801) | 5 | E | 0 | NULL | NULL | NULL | NULL | NULL |
| 1637 | VTech | VT5888 (VT5881) | 5 | E | 0 | NULL | NULL | NULL | NULL | NULL |
| 1249 | VTech | VT9161 | 5 | L | 0 | 24.9900 | 124.95 | 49.9820 | 249.91 | 5 |
| 1617 | VTech | VT921 | 5 | R | 0 | 17.9000 | 161.10 | 51.6344 | 464.71 | 9 |
| 1250 | VTech | VT9241 | 5 | L | 0 | 23.0000 | 299.00 | 69.9869 | 909.83 | 13 |
....
578 rows in set (0.36 sec)
>>>select count(*) from invProducts where active='Y';
+----------+
| count(*) |
+----------+
| 752 |
+----------+
There should be 752 rows in the resultset for the outer join query but it's only returning 578.
However, when I remove the date from the where clause, i do get a complete resultset.
>>>SELECT SQL_NO_CACHE b.inventory_id as inventory_id,
b.manufacturer as manu, b.sku as sku, b.mmspct as mmspct, b.stype as stype, LENGTH(b.memo)=0 as hasNoMemo,
sum(IF(a.cost>0,a.cost,a.price)*a.quantity)/sum(a.quantity) as cost,
sum(IF(a.cost>0,a.cost,a.price)*a.quantity) as tcost,
sum(a.price*a.quantity)/sum(a.quantity) as price,
sum(a.price*a.quantity) as tprice,
sum(a.quantity) as quantity
FROM invOrderItems as a RIGHT JOIN invProducts as b USING(inventory_id)
WHERE b.active='Y'
GROUP BY b.inventory_id ORDER BY manu, sku
....
| 1636 | VTech | VT5888 (VT5801) | 5 | E | 0 | NULL | NULL | NULL | NULL | NULL |
| 1637 | VTech | VT5888 (VT5881) | 5 | E | 0 | NULL | NULL | NULL | NULL | NULL |
| 1249 | VTech | VT9161 | 5 | L | 0 | 24.9900 | 124.95 | 49.9820 | 249.91 | 5 |
| 1617 | VTech | VT921 | 5 | R | 0 | 17.9000 | 161.10 | 51.6344 | 464.71 | 9 |
| 1250 | VTech | VT9241 | 5 | L | 0 | 23.0000 | 299.00 | 69.9869 | 909.83 | 13 |
....
752 rows in set (0.72 sec)
The WHERE clause on table "a" should not affect the size of the resultset in my case. null should cover the rows that don't exist in table "a".
How to repeat:
Reference the above queries
Suggested fix:
I have no clue... either this bug is weird or my knowledge of how outer joins work is flawed.
