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.
--
Ubuntu-server-bugs mailing list
Ubuntu-server-bugs@lists.ubuntu.com
Modify settings or unsubscribe at:
https://lists.ubuntu.com/mailman/listinfo/ubuntu-server-bugs
