[Maria-developers] MWL#182: a problem select_type column, SIMPLE vs PRIMARY
![](https://secure.gravatar.com/avatar/bccaf81a23eecaecd9feaf01c7c42ff4.jpg?s=120&d=mm&r=g)
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
![](https://secure.gravatar.com/avatar/3c2329333dd5898f2e72818af773243d.jpg?s=120&d=mm&r=g)
Hi, Sergey! On Aug 27, Sergey Petrunya wrote:
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. ... 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
I think it's fine as an interim solution. To have the problem recorded somewhere, please report a bug about EXPLAIN reporting incorrect select_type.
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
Is this SIMPLE/PRIMARY the only difference between EXPLAIN SELECT and SHOW EXPLAIN that you see?
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 makes me think that there may be other differences betwen them. And that eventually explain select should be moved down the code path. Which, I expect, will allow to remove quite a lot of duplicated code :) Regards, Sergei
![](https://secure.gravatar.com/avatar/06b8a7f236cf59aa9a83cab1a0551ca8.jpg?s=120&d=mm&r=g)
Hi!
"Sergey" == Sergey Petrunya
writes:
Sergey> Hello, Sergey> When coding MWL#182, I've found out that it is very difficult to print Sergey> the same value of select_type as EXPLAIN does. <cut> Sergey> Future solution #1: get rid of SIMPLE Sergey> ------------------------------------- Sergey> I don't see a value of having select_type=SIMPLE (other than term "simple" Sergey> being encouraging for novice users:) We could change SIMPLE to PRIMARY Sergey> everywhere and get rid of the problem Sergey> Future solution #2: use SIMPLE/PRIMARY approach of SHOW EXPLAIN Sergey> --------------------------------------------------------------- Sergey> EXPLAIN SELECT produces incorrect values because it calls Sergey> st_select_lex::set_explain_type() before the query rewrites are done, so Sergey> it can't see that Sergey> - used VIEWs have subqueries Sergey> - used subqueries will be flattened Sergey> - etc. Sergey> SHOW EXPLAIN operates on a query that is being executed, and that alone Sergey> guarantees its output is closer to reality. This means, EXPLAIN SELECT output Sergey> must be changed to match SHOW EXPLAIN output. This is a lot of changes Sergey> across a number of .result files. It don't think it's a big problem if 'show explain' have a slightly different result than 'explain select' for the time being (if it's more correct). Regards, Monty
participants (3)
-
Michael Widenius
-
Sergei Golubchik
-
Sergey Petrunya