[Maria-developers] Philip please test: Duplicate Elimination for outer joins + semi joins
Hello Philip, I'd like to get the tree at lp:~maria-captains/maria/5.3-subqueries-mwl90 tested for Duplicate Elimination with outer+semi-join processing. I'm interested in crashes, as well as wrong query result bugs. The code path that needs to be tested is selected with this @@optimizer_switch setting: semijoin=on,materialization=off,firstmatch=off,loosescan=off this set of switches disables all semi-join subquery strategies except DuplicateElimination, so you should have no problem with hitting the If you also set "semijoin=off" then you get the execution path that we assume will always produce correct results. Query pattern SELECT .... FROM complex_join WHERE top_where AND (SELECT ... FROM complex_join WHERE ...) where - subqueries must be "mergeable semi-joins": = no aggregates, unions, or ORDER BYs - both correlated and ucorrelated subqueries are OK. - complex_join is arbitrary join structure (comma join, "t1 JOIN t2", various kinds of outer joins - everything should work) - multiple sibling subqueries, as well as nested subqueries are covered by what you're testing. EXPLAIN - Should have id=='1' && select_type='PRIMARY' on all lines. - Should use Start Temporary/End temporary. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
ok, I am on it. ----- Original Message ----- From: "Sergey Petrunya" <psergey@askmonty.org> To: "Philip Stoev" <pstoev@askmonty.org> Cc: <maria-developers@lists.launchpad.net> Sent: Tuesday, June 28, 2011 9:26 AM Subject: Philip please test: Duplicate Elimination for outer joins + semijoins
Hello Philip,
I'd like to get the tree at lp:~maria-captains/maria/5.3-subqueries-mwl90 tested for Duplicate Elimination with outer+semi-join processing.
I'm interested in crashes, as well as wrong query result bugs. The code path that needs to be tested is selected with this @@optimizer_switch setting:
semijoin=on,materialization=off,firstmatch=off,loosescan=off
this set of switches disables all semi-join subquery strategies except DuplicateElimination, so you should have no problem with hitting the
If you also set "semijoin=off" then you get the execution path that we assume will always produce correct results.
Query pattern
SELECT .... FROM complex_join WHERE top_where AND (SELECT ... FROM complex_join WHERE ...)
where - subqueries must be "mergeable semi-joins": = no aggregates, unions, or ORDER BYs - both correlated and ucorrelated subqueries are OK. - complex_join is arbitrary join structure (comma join, "t1 JOIN t2", various kinds of outer joins - everything should work)
- multiple sibling subqueries, as well as nested subqueries are covered by what you're testing.
EXPLAIN - Should have id=='1' && select_type='PRIMARY' on all lines. - Should use Start Temporary/End temporary.
BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
Hi, I have tested your tree as much as I could and filed a few bugs. I can only resume testing after those bugs are fixed. Preferably *all* of them, this will also prevent the bugs from getting so stale that they are no longer repeatable. For a list of all the dimensions that are being tested and the complexity in each direction, see: http://forge.mysql.com/wiki/RandomQueryGeneratorOptimizerTesting I could not fully test the following 2 things: - nested subqueries, blocked due to 802979 - views and derived tables, blocked due to crashes
semijoin=on,materialization=off,firstmatch=off,loosescan=off
this set of switches disables all semi-join subquery strategies except DuplicateElimination, so you should have no problem with hitting the
If you also set "semijoin=off" then you get the execution path that we assume will always produce correct results.
Yes, I am doing exactly that.
Query pattern
SELECT .... FROM complex_join WHERE top_where AND (SELECT ... FROM complex_join WHERE ...)
Yes, this pattern is covered
where - subqueries must be "mergeable semi-joins": = no aggregates, unions, or ORDER BYs
Yep.
- both correlated and ucorrelated subqueries are OK.
Yep.
- complex_join is arbitrary join structure (comma join, "t1 JOIN t2", various kinds of outer joins - everything should work)
We do joins of up to 3 tables in the inner and the outer query. Both inner and outer joins, both comma joins and joins with ON .
- multiple sibling subqueries, as well as nested subqueries are covered by what you're testing.
As noted above, nested subqueries are blocked. Multiple subqueries per query are tested.
EXPLAIN - Should have id=='1' && select_type='PRIMARY' on all lines. - Should use Start Temporary/End temporary.
Yes I am able to hit this EXPLAIN reliably.
participants (2)
-
Philip Stoev -
Sergey Petrunya