bk commit into 5.0 tree (gkodinov:1.2501) BUG#30377

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-08-28 18:51:03+03:00, gkodinov@stripped +4 -0
  Bug #30377: EXPLAIN loses last_query_cost when used with UNION
  
  Currently the Last_query_cost session status variable shows
  only the cost of a single flat subselect. For complex queries
  (with subselects or unions etc) Last_query_cost is not valid
  as it was showing the cost for the last optimized subselect.
  Fixed by reseting to zero Last_query_cost when the complete
  cost of the query cannot be determined.
  Last_query_cost will be non-zero only for single flat queries.

  mysql-test/r/status.result@stripped, 2007-08-28 18:51:02+03:00, gkodinov@stripped +48 -0
    Bug #30377: test case

  mysql-test/t/status.test@stripped, 2007-08-28 18:51:02+03:00, gkodinov@stripped +32 -0
    Bug #30377: test case

  sql/sql_lex.h@stripped, 2007-08-28 18:51:02+03:00, gkodinov@stripped +22 -0
    Bug #30377: helper function

  sql/sql_select.cc@stripped, 2007-08-28 18:51:02+03:00, gkodinov@stripped +6 -2
    Bug #30377: don't assign cost if not on single level statement

diff -Nrup a/mysql-test/r/status.result b/mysql-test/r/status.result
--- a/mysql-test/r/status.result	2006-04-12 16:37:50 +03:00
+++ b/mysql-test/r/status.result	2007-08-28 18:51:02 +03:00
@@ -43,3 +43,51 @@ SHOW STATUS LIKE 'max_used_connections';
 Variable_name	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;
diff -Nrup a/mysql-test/t/status.test b/mysql-test/t/status.test
--- a/mysql-test/t/status.test	2007-06-27 03:33:50 +03:00
+++ b/mysql-test/t/status.test	2007-08-28 18:51:02 +03:00
@@ -139,4 +139,36 @@ 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 -Nrup a/sql/sql_lex.h b/sql/sql_lex.h
--- a/sql/sql_lex.h	2007-08-15 20:24:06 +03:00
+++ b/sql/sql_lex.h	2007-08-28 18:51:02 +03:00
@@ -1257,6 +1257,28 @@ typedef struct st_lex : public Query_tab
 
   void reset_n_backup_query_tables_list(Query_tables_list *backup);
   void restore_backup_query_tables_list(Query_tables_list *backup);
+
+  /**
+    @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 -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-08-15 20:24:06 +03:00
+++ b/sql/sql_select.cc	2007-08-28 18:51:02 +03:00
@@ -4369,9 +4369,13 @@ choose_plan(JOIN *join, table_map join_t
 
   /* 
     Store the cost of this query into a user variable
-    Don't update last_query_cost for 'show status' command
+    Don't update last_query_cost for 'show status' command.
+    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.
   */
-  if (join->thd->lex->orig_sql_command != SQLCOM_SHOW_STATUS)
+  if (join->thd->lex->orig_sql_command != SQLCOM_SHOW_STATUS &&
+      join->thd->lex->is_single_level_stmt())
     join->thd->status_var.last_query_cost= join->best_read;
   DBUG_RETURN(FALSE);
 }