mysql queries "lose" results

[Martin Wimmer]

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

[Mathias Gug (mathiaz)]

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

--
Mathias Gug
Ubuntu Developer http://www.ubuntu.com

[Martin Wimmer (martin-wimmer)]

ok, thank you for your quick reply. I now created an import script and tested it.

With it I could also reproduce this bug on intrepid.

To see how the results of the query should normally look like, try to insert a dummy entry into the temp table:
INSERT INTO `plib_objects_plib_objects_dbo_temp` (`oid`, `expires`) VALUES
(-1, '2030-01-01 00:00:00');

After creating it the bug disappears. If you delete it, the bug appears again.

[Mathias Gug (mathiaz)]

Thanks for providing the database creation script. Could you also post the expected result of the query as well as the actual result?

[Martin Wimmer (martin-wimmer)]

ok. I changed the query a bit so the results are easier to compare:

When the bug appears, most rows in the result set of the following query contain NULL as a value, and we have 369 rows.

If I insert the dummy entry in the table plib_objects_plib_objects_dbo_temp, then the bug disappears and now all rows contain numbers (There is not a single NULL row). We also have more rows (430).

The test was done with the following query:
SELECT shop_element_oid
FROM ((`plib_objects_plib_objects_dbo` a20_1_dbo, ((`plib_objects_at_telacc_demopool_shops_product` a20_1_product)
LEFT JOIN ((`plib_objects_at_telacc_demopool_shops_product_distributor` a20_1_distributor) )
ON a20_1_distributor.part_nr = a20_1_product.part_nr),
(`plib_objects_at_telacc_demopool_shops_shop_element` a20_1_shop_element) )
LEFT JOIN ((`plib_objects_plib_objects_dbo` a21_1_dbo, (`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 ((`plib_objects_plib_objects_dbo` a23_1_dbo, (`plib_objects_at_telacc_demopool_shops_shop_element` a23_1_shop_element) , (`plib_objects_at_telacc_demopool_shops_category` a23_1_shop_category) )
 LEFT JOIN ((`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 ((`plib_objects_plib_objects_dbo_temp` a20_1_temp) )
ON a20_1_temp.oid = a20_1_dbo.oid )
LEFT JOIN ((`plib_objects_at_telacc_demopool_shops_shop_element__shop` a20_1_shops)
 LEFT JOIN ((`plib_objects_plib_objects_dbo` a22_1_dbo, (`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

[Chuck Short (zulcss)]

Can you try this under mysql 5.1 please?

Thanks
chuck

[Martin Wimmer (martin-wimmer)]

ok, tried it under mysql 5.1.37-1ubuntu5.1 (The Version in Ubuntu Karmic) I'll try it again under Ubuntu Lucid, as soon as I have it installed...

[Chuck Short (zulcss)]

You didnt mention if it solved your problem.

chuck

[Martin Wimmer (martin-wimmer)]

ok, tested it now. Still not fixed under lucid