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