maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

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