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_ty... 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