mysql queries "lose" results
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
mysql-dfsg-5.1 (Ubuntu) |
Confirmed
|
Low
|
Unassigned |
Bug Description
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_
LEFT JOIN ((`at_telacc_
ON a20_1_distribut
(`at_telacc_
LEFT JOIN ((`at_telacc_
ON a21_1_dbo_type.oid = a21_1_dbo.oid AND a20_1_dbo.type_oid = a21_1_dbo.oid
LEFT JOIN ((`at_telacc_
LEFT JOIN ((`at_telacc_
ON a23_1_children.oid = a23_1_dbo.oid)
ON a23_1_shop_
LEFT JOIN ((`at_telacc_
ON a20_1_temp.oid = a20_1_dbo.oid )
LEFT JOIN ((`at_telacc_
LEFT JOIN ((`at_telacc_
ON ((a20_1_shops.flags & 4) = 4) AND a22_1_shop.oid = a22_1_dbo.oid AND a20_1_shops.
ON a20_1_shops.oid = a20_1_dbo.oid
WHERE
a20_1_product.oid = a20_1_dbo.oid AND a20_1_shop_
Changed in mysql-dfsg-5.0: | |
status: | Incomplete → Confirmed |
status: | Confirmed → New |
Changed in mysql-dfsg-5.0 (Ubuntu): | |
status: | Incomplete → New |
Changed in mysql-dfsg-5.0 (Ubuntu): | |
status: | New → Triaged |
status: | Triaged → Confirmed |
Thank you for taking the time to report this bug and helping to make
Ubuntu better.
On Tue, Mar 31, 2009 at 11:48:19AM -0000, Martin Wimmer wrote:
> I can provide a database setup script to create the database if needed.
Yes. Please provide a database setup script in order to facilitate
creating a reproducible test case. Thank you.
status incomplete
-- www.ubuntu. com
Mathias Gug
Ubuntu Developer http://