now all tables of query are locked in one place (including derived tables)

fixed BUG#2120 and other problem with EXPLAINing derived tables


mysql-test/r/derived.result:
  correct tables names & Co in derived tables
  test case for BUG#2120
mysql-test/t/derived.test:
  test case for BUG#2120
sql/mysql_priv.h:
  derived tables processing moved after open/locking all tables (in open_and_lock_tables)
sql/repl_failsafe.cc:
  correct initialization of TABLE_LIST
sql/sql_acl.cc:
  used simple table opening without derived table processing to avoid unneeded initialization of SELECT_LEX
sql/sql_base.cc:
  derived tables processing moved after open/locking all tables (in open_and_lock_tables)
sql/sql_delete.cc:
  all tables processing is done during opening
sql/sql_derived.cc:
  derived tables processing moved after open/locking all tables (in open_and_lock_tables) to sutisfy "all query tables locking" at the moment
sql/sql_insert.cc:
  all tables processing is done during opening
  correct initialization of TABLE_LIST
sql/sql_lex.cc:
  now table list will be created for whole query
  layout fix
  correct check of updated table in subqueries
sql/sql_lex.h:
  now table list will be created for whole query
  correct check of updated table in subqueries
sql/sql_olap.cc:
  THIS FUNCTION IS USED NOWHERE
  it will be good to remove it at all (handle_olaps)
sql/sql_parse.cc:
  derived tables processing moved after open/locking all tables (in open_and_lock_tables)
sql/sql_prepare.cc:
  new creating list parameters
  all tables processing is done during opening
sql/sql_select.cc:
  all tables processing is done during opening
sql/sql_select.h:
  now it used only within file where is defined
sql/sql_udf.cc:
  used simple table opening without derived table processing to avoid unneeded initialization of SELECT_LEX
sql/sql_update.cc:
  all tables processing is done during opening

1 files changed, 9 insertions, 0 deletions

diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 154fc4b3834..b412555f1e8 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -183,3 +183,12 @@ CREATE TABLE t3 (
 INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
 select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
 drop table t1, t2, t3;
+
+
+#
+# explain derived
+#
+CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
+INSERT INTO t1 VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
+SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;