[Bug 352321] [NEW] mysql queries "lose" results

[Martin Wimmer]

Public bug reported:

Binary package hint: mysql-server-5.0

Problem tested on: Ubuntu Hardy Heron (Server)

On certain (mostly large) queries that use nested joins, when using LEFT
JOIN's with tables which are empty, other joins seem to also to be
evaluated as empty although they shouldn't be. As soon as I add a dummy
entry to the empty table (which is not joined because of the on clause)
the results are correct

This seems to be a problem of the query optimizer.

Following example query leads to the problems. (The empty table is
a20_1_temp. The table join for a20_1_shop_element is then evaluated as
empty)

Unfortunately I couldn't make the query less complex as I did here as the bug doesn't appear then any more.
I can provide a database setup script to create the database if needed.

SELECT *
FROM ((`at_telacc_demopool`.`plib_objects_plib_objects_dbo` a20_1_dbo, ((`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_product` a20_1_product) 
LEFT JOIN ((`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_product_distributor` a20_1_distributor) ) 
ON a20_1_distributor.part_nr = a20_1_product.part_nr),
(`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_shop_element` a20_1_shop_element) ) 
LEFT JOIN ((`at_telacc_demopool`.`plib_objects_plib_objects_dbo` a21_1_dbo, (`at_telacc_demopool`.`plib_objects_plib_objects_dbo_type` a21_1_dbo_type) ) ) 
ON a21_1_dbo_type.oid = a21_1_dbo.oid AND a20_1_dbo.type_oid = a21_1_dbo.oid 
LEFT JOIN ((`at_telacc_demopool`.`plib_objects_plib_objects_dbo` a23_1_dbo, (`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_shop_element` a23_1_shop_element) , (`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_category` a23_1_shop_category) ) 
	LEFT JOIN ((`at_telacc_demopool`.`plib_objects__telacc_demopool_shops_category__shop_element` a23_1_children) ) 
	ON a23_1_children.oid = a23_1_dbo.oid) 
ON a23_1_shop_element.oid = a23_1_dbo.oid AND a23_1_shop_category.oid = a23_1_dbo.oid AND a20_1_dbo.oid = a23_1_children.shop_element_oid 
LEFT JOIN ((`at_telacc_demopool`.`plib_objects_plib_objects_dbo_temp` a20_1_temp) ) 
ON a20_1_temp.oid = a20_1_dbo.oid )
LEFT JOIN ((`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_shop_element__shop` a20_1_shops) 
	LEFT JOIN ((`at_telacc_demopool`.`plib_objects_plib_objects_dbo` a22_1_dbo, (`at_telacc_demopool`.`plib_objects_at_telacc_demopool_shops_shop` a22_1_shop) ) ) 
	ON ((a20_1_shops.flags & 4) = 4) AND a22_1_shop.oid = a22_1_dbo.oid AND a20_1_shops.shop_oid = a22_1_dbo.oid) 
ON a20_1_shops.oid = a20_1_dbo.oid 
WHERE 
 a20_1_product.oid = a20_1_dbo.oid AND a20_1_shop_element.oid = a20_1_dbo.oid

** Affects: mysql-dfsg-5.0 (Ubuntu)
     Importance: Undecided
         Status: New

-- 
mysql queries "lose" results
https://bugs.launchpad.net/bugs/352321
You received this bug notification because you are a member of Ubuntu
Server Team, which is subscribed to mysql-dfsg-5.0 in ubuntu.