diff options
| -rw-r--r-- | mysql-test/r/status.result | 48 | ||||
| -rw-r--r-- | mysql-test/t/status.test | 32 | ||||
| -rw-r--r-- | sql/sql_lex.h | 22 | ||||
| -rw-r--r-- | sql/sql_select.cc | 6 |
4 files changed, 107 insertions, 1 deletions
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 8a2c3ae344e..db75044ee5d 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -87,6 +87,54 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_us VARIABLE_NAME VARIABLE_VALUE MAX_USED_CONNECTIONS 4 SET GLOBAL thread_cache_size=@save_thread_cache_size; +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1), (2); +SELECT a FROM t1 LIMIT 1; +a +1 +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 2.402418 +EXPLAIN SELECT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 2.402418 +SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a; +a +1 +2 +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 0.000000 +EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 UNION t1 ALL NULL NULL NULL NULL 2 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 0.000000 +SELECT a IN (SELECT a FROM t1) FROM t1 LIMIT 1; +a IN (SELECT a FROM t1) +1 +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 0.000000 +SELECT (SELECT a FROM t1 LIMIT 1) x FROM t1 LIMIT 1; +x +1 +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 0.000000 +SELECT * FROM t1 a, t1 b LIMIT 1; +a a +1 1 +SHOW SESSION STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 4.805836 +DROP TABLE t1; show status like 'com_show_status'; Variable_name Value Com_show_status 3 diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 7fd995e70c7..1cd5aa1726a 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -186,6 +186,38 @@ disconnect con3; disconnect con2; disconnect con1; + +# +# Bug #30377: EXPLAIN loses last_query_cost when used with UNION +# + +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1), (2); + +SELECT a FROM t1 LIMIT 1; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +EXPLAIN SELECT a FROM t1; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +SELECT a IN (SELECT a FROM t1) FROM t1 LIMIT 1; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +SELECT (SELECT a FROM t1 LIMIT 1) x FROM t1 LIMIT 1; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +SELECT * FROM t1 a, t1 b LIMIT 1; +SHOW SESSION STATUS LIKE 'Last_query_cost'; + +DROP TABLE t1; + + # End of 5.0 tests # diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 057d59d3ccf..d8549be44c7 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1799,6 +1799,28 @@ typedef struct st_lex : public Query_tables_list bool table_or_sp_used(); bool is_partition_management() const; + + /** + @brief check if the statement is a single-level join + @return result of the check + @retval TRUE The statement doesn't contain subqueries, unions and + stored procedure calls. + @retval FALSE There are subqueries, UNIONs or stored procedure calls. + */ + bool is_single_level_stmt() + { + /* + This check exploits the fact that the last added to all_select_list is + on its top. So select_lex (as the first added) will be at the tail + of the list. + */ + if (&select_lex == all_selects_list && !sroutines.records) + { + DBUG_ASSERT(!all_selects_list->next_select_in_list()); + return TRUE; + } + return FALSE; + } } LEX; struct st_lex_local: public st_lex diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 95ae15a51ec..b903e7ae13c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4505,8 +4505,12 @@ choose_plan(JOIN *join, table_map join_tables) /* Store the cost of this query into a user variable + Don't update last_query_cost for statements that are not "flat joins" : + i.e. they have subqueries, unions or call stored procedures. + TODO: calculate a correct cost for a query with subqueries and UNIONs. */ - join->thd->status_var.last_query_cost= join->best_read; + if (join->thd->lex->is_single_level_stmt()) + join->thd->status_var.last_query_cost= join->best_read; DBUG_RETURN(FALSE); } |