maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hello,

When coding MWL#182, I've found out that it is very difficult to print
the same value of select_type as EXPLAIN does. 

Problem description
-------------------

Moreover, any attempt to unify EXPLAIN and SHOW EXPLAIN code cause numerous
test failures because of changed select_type.


http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_select_type
defines:
- SIMPLE 	Simple SELECT (not using UNION or subqueries)
- PRIMARY 	Outermost SELECT


In practice, this is is not always true. As a most striking example, grep for
this query in subselect4.test:

PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
   (SELECT EMPNUM
    FROM t3
    WHERE PNUM IN
       (SELECT PNUM
        FROM t2
        WHERE PTYPE = 'Design'))";

The first execution will produce rows with select_type='PRIMARY', while the
second will produce the same rows with select_type='SIMPLE'. Apparently, one 
of these is wrong.

If you do a plain select from a VIEW that has a subquery 

  create view V as select ... (subquery) ...;
  explain select * from V;

you get EXPLAIN output which starts with a row that select_type='SIMPLE' and
continues with rows describing the subquery, which causes a self-contradiction.


Current solution
----------------
My approach to dealing with this is as follows:
- MWL#182 code should not make changes to output of regular EXPLAIN SELECT
  queries.
- Outputs of SHOW EXPLAIN and EXPLAIN SELECT of the same query may be slightly
  different

This causes some ugliness in the code though, because I had to keep two
ways to generate select_type values.

I think this should be ok (if you disagree, please let me know)


Future solution #1: get rid of SIMPLE
-------------------------------------

I don't see a value of having select_type=SIMPLE (other than term "simple"
being encouraging for novice users:)  We could change SIMPLE to PRIMARY
everywhere and get rid of the problem


Future solution #2: use SIMPLE/PRIMARY approach of SHOW EXPLAIN
---------------------------------------------------------------
EXPLAIN SELECT produces incorrect values because it calls 
st_select_lex::set_explain_type() before the query rewrites are done, so 
it can't see that
- used VIEWs have subqueries
- used subqueries will be flattened
- etc.

SHOW EXPLAIN operates on a query that is being executed, and that alone
guarantees its output is closer to reality. This means, EXPLAIN SELECT output
must be changed to match SHOW EXPLAIN output.  This is a lot of changes 
across a number of .result files.

BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog