From: Mattias Jonsson
Date: January 5 2012 9:54am
Subject: bzr push into mysql-trunk branch (mattias.jonsson:3702 to 3703) WL#4443
List-Archive: http://lists.mysql.com/commits/142296
Message-Id: <201201050954.q059svcF005514@acsmt357.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3703 Mattias Jonsson	2012-01-05
      WL#4443
      
      sql_view.cc:
      no need of locking tables during create view.
      
      Updated results files after derived handling was removed
      from update (now the explain and execution is the same
      as for the same select).
      
      updated results files affected by bug#13559657.

    modified:
      mysql-test/r/innodb_explain_non_select_none.result
      mysql-test/r/myisam_explain_non_select_none.result
      mysql-test/r/partition_locking.result
      mysql-test/suite/parts/inc/partition-dml-1-9.inc
      mysql-test/suite/parts/r/partition-dml-1-9-innodb.result
      mysql-test/suite/parts/r/partition-dml-1-9-myisam.result
      mysql-test/t/partition_locking.test
      sql/sql_view.cc
 3702 Mattias Jonsson	2012-01-05
      WL#4443
      
      sql_lex.cc:
      Added fix for bug#13559657
      (explicit partiton selection not included in views)
      
      sql_update.cc:
      removed derived handling (cleanup)
      
      sql_view.cc:
      added delayed locking to be able to prune locks during create.

    modified:
      mysql-test/r/partition_explicit_prune.result
      mysql-test/r/partition_locking.result
      mysql-test/t/partition_explicit_prune.test
      mysql-test/t/partition_locking.test
      sql/sql_lex.cc
      sql/sql_update.cc
      sql/sql_view.cc
=== modified file 'mysql-test/r/innodb_explain_non_select_none.result'
--- a/mysql-test/r/innodb_explain_non_select_none.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/innodb_explain_non_select_none.result	revid:mattias.jonsson@stripped
@@ -2784,14 +2784,14 @@ INSERT INTO t2 VALUES (1), (2), (3);
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	auto_key0	auto_key0	5	func	2	Using index
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	auto_key0	auto_key0	5	func	2	100.00	Using index
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
@@ -2817,8 +2817,8 @@ Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	2
-Handler_read_rnd_next	13
+Handler_read_key	5
+Handler_read_rnd_next	8
 Handler_update	1
 Handler_write	1
 Sort_rows	3

=== modified file 'mysql-test/r/myisam_explain_non_select_none.result'
--- a/mysql-test/r/myisam_explain_non_select_none.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/myisam_explain_non_select_none.result	revid:mattias.jonsson@stripped
@@ -2653,14 +2653,14 @@ INSERT INTO t2 VALUES (1), (2), (3);
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	auto_key0	auto_key0	5	func	2	Using index
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	auto_key0	auto_key0	5	func	2	100.00	Using index
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
@@ -2684,7 +2684,8 @@ Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_rnd_next	13
+Handler_read_key	3
+Handler_read_rnd_next	8
 Handler_update	1
 Handler_write	1
 Sort_rows	3

=== modified file 'mysql-test/r/partition_locking.result'
--- a/mysql-test/r/partition_locking.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/partition_locking.result	revid:mattias.jonsson@stripped
@@ -1368,46 +1368,36 @@ CREATE VIEW v1_25 AS SELECT a, b FROM t1
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	1
-HANDLER_EXTERNAL_LOCK	6
 HANDLER_WRITE	17
-# 6 locks (1 table, 2 partitions lock/unlock)
+# No locks!
 FLUSH STATUS;
 CREATE VIEW v1_25_check AS SELECT a, b FROM t1 PARTITION (p2, p5) t1_alias WITH CHECK OPTION;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	1
-HANDLER_EXTERNAL_LOCK	6
 HANDLER_WRITE	17
-# 6 locks (1 table, 2 partitions lock/unlock)
+# No locks!
 FLUSH STATUS;
 CREATE VIEW v1_9 AS SELECT a, b FROM t1 WHERE a = 9;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	1
-HANDLER_EXTERNAL_LOCK	4
 HANDLER_WRITE	17
-# 4 locks (1 table, 1 partitions lock/unlock)
+# No locks!
 FLUSH STATUS;
 CREATE VIEW v1_9_check AS SELECT a, b FROM t1 WHERE a = 9 WITH CHECK OPTION;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	1
-HANDLER_EXTERNAL_LOCK	4
 HANDLER_WRITE	17
-# 4 locks (1 table, 1 partitions lock/unlock)
+# No locks!
 FLUSH STATUS;
 CREATE VIEW v1_all AS SELECT a, b FROM t1;
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_COMMIT	1
-HANDLER_EXTERNAL_LOCK	28
 HANDLER_WRITE	17
-# 28 locks (1 table, 13 partitions lock/unlock)
+# No locks!
 SELECT TABLE_NAME, CHECK_OPTION, IS_UPDATABLE, VIEW_DEFINITION
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 'v1_%';

=== modified file 'mysql-test/suite/parts/inc/partition-dml-1-9.inc'
--- a/mysql-test/suite/parts/inc/partition-dml-1-9.inc	revid:mattias.jonsson@stripped
+++ b/mysql-test/suite/parts/inc/partition-dml-1-9.inc	revid:mattias.jonsson@stripped
@@ -16,11 +16,14 @@ let $TABLENAME= t2;
 GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw';
 
 connect (session1, localhost, test_user_1,'testpw',test);
---sorted_result
 CREATE VIEW v1 AS
 SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2 
 WHERE t1.a = t2.a;
+--sorted_result
 SELECT * FROM v1;
+--sorted_result
+SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2
+WHERE t1.a = t2.a;
 
 --sorted_result
 SELECT * FROM t1 PARTITION (`p0-29`);

=== modified file 'mysql-test/suite/parts/r/partition-dml-1-9-innodb.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-9-innodb.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/suite/parts/r/partition-dml-1-9-innodb.result	revid:mattias.jonsson@stripped
@@ -126,38 +126,25 @@ SELECT t1.a, t2.b FROM t1 PARTITION (`p0
 WHERE t1.a = t2.a;
 SELECT * FROM v1;
 a	b
--3	(pNeg-)subp0
--4	(pNeg-)subp0
--1	(pNeg-)subp0
--2	(pNeg-)subp0
-3	(p0-29-)subp3
+1	(p0-29-)subp3
+2	(p0-29-)subp3
 21	(p0-29-)subp5
+22	(p0-29-)subp5
+23	(p0-29-)subp5
 24	(p0-29-)subp5
-1	(p0-29-)subp3
+3	(p0-29-)subp3
 4	(p0-29-)subp3
-22	(p0-29-)subp5
+SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2
+WHERE t1.a = t2.a;
+a	b
+1	(p0-29-)subp3
 2	(p0-29-)subp3
+21	(p0-29-)subp5
+22	(p0-29-)subp5
 23	(p0-29-)subp5
-33	(p30-299-)subp6
-231	(p30-299-)subp8
-234	(p30-299-)subp8
-31	(p30-299-)subp6
-34	(p30-299-)subp6
-232	(p30-299-)subp8
-32	(p30-299-)subp6
-233	(p30-299-)subp8
-303	(p300-2999-)subp8
-301	(p300-2999-)subp8
-304	(p300-2999-)subp8
-302	(p300-2999-)subp8
-3003	(p3000-299999-)subp12
-299997	(p3000-299999-)subp14
-3001	(p3000-299999-)subp12
-3004	(p3000-299999-)subp12
-299998	(p3000-299999-)subp14
-3002	(p3000-299999-)subp12
-299996	(p3000-299999-)subp14
-299999	(p3000-299999-)subp14
+24	(p0-29-)subp5
+3	(p0-29-)subp3
+4	(p0-29-)subp3
 SELECT * FROM t1 PARTITION (`p0-29`);
 a	b
 1	(p0-29-)subp3

=== modified file 'mysql-test/suite/parts/r/partition-dml-1-9-myisam.result'
--- a/mysql-test/suite/parts/r/partition-dml-1-9-myisam.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/suite/parts/r/partition-dml-1-9-myisam.result	revid:mattias.jonsson@stripped
@@ -126,38 +126,25 @@ SELECT t1.a, t2.b FROM t1 PARTITION (`p0
 WHERE t1.a = t2.a;
 SELECT * FROM v1;
 a	b
--3	(pNeg-)subp0
--4	(pNeg-)subp0
--1	(pNeg-)subp0
--2	(pNeg-)subp0
-3	(p0-29-)subp3
+1	(p0-29-)subp3
+2	(p0-29-)subp3
 21	(p0-29-)subp5
+22	(p0-29-)subp5
+23	(p0-29-)subp5
 24	(p0-29-)subp5
-1	(p0-29-)subp3
+3	(p0-29-)subp3
 4	(p0-29-)subp3
-22	(p0-29-)subp5
+SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2
+WHERE t1.a = t2.a;
+a	b
+1	(p0-29-)subp3
 2	(p0-29-)subp3
+21	(p0-29-)subp5
+22	(p0-29-)subp5
 23	(p0-29-)subp5
-33	(p30-299-)subp6
-231	(p30-299-)subp8
-234	(p30-299-)subp8
-31	(p30-299-)subp6
-34	(p30-299-)subp6
-232	(p30-299-)subp8
-32	(p30-299-)subp6
-233	(p30-299-)subp8
-303	(p300-2999-)subp8
-301	(p300-2999-)subp8
-304	(p300-2999-)subp8
-302	(p300-2999-)subp8
-3003	(p3000-299999-)subp12
-299997	(p3000-299999-)subp14
-3001	(p3000-299999-)subp12
-3004	(p3000-299999-)subp12
-299998	(p3000-299999-)subp14
-3002	(p3000-299999-)subp12
-299996	(p3000-299999-)subp14
-299999	(p3000-299999-)subp14
+24	(p0-29-)subp5
+3	(p0-29-)subp3
+4	(p0-29-)subp3
 SELECT * FROM t1 PARTITION (`p0-29`);
 a	b
 1	(p0-29-)subp3

=== modified file 'mysql-test/t/partition_locking.test'
--- a/mysql-test/t/partition_locking.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/partition_locking.test	revid:mattias.jonsson@stripped
@@ -618,23 +618,23 @@ eval $get_handler_status_counts;
 FLUSH STATUS;
 CREATE VIEW v1_25 AS SELECT a, b FROM t1 PARTITION (p2, p5);
 eval $get_handler_status_counts;
---echo # 6 locks (1 table, 2 partitions lock/unlock)
+--echo # No locks!
 FLUSH STATUS;
 CREATE VIEW v1_25_check AS SELECT a, b FROM t1 PARTITION (p2, p5) t1_alias WITH CHECK OPTION;
 eval $get_handler_status_counts;
---echo # 6 locks (1 table, 2 partitions lock/unlock)
+--echo # No locks!
 FLUSH STATUS;
 CREATE VIEW v1_9 AS SELECT a, b FROM t1 WHERE a = 9;
 eval $get_handler_status_counts;
---echo # 4 locks (1 table, 1 partitions lock/unlock)
+--echo # No locks!
 FLUSH STATUS;
 CREATE VIEW v1_9_check AS SELECT a, b FROM t1 WHERE a = 9 WITH CHECK OPTION;
 eval $get_handler_status_counts;
---echo # 4 locks (1 table, 1 partitions lock/unlock)
+--echo # No locks!
 FLUSH STATUS;
 CREATE VIEW v1_all AS SELECT a, b FROM t1;
 eval $get_handler_status_counts;
---echo # 28 locks (1 table, 13 partitions lock/unlock)
+--echo # No locks!
 SELECT TABLE_NAME, CHECK_OPTION, IS_UPDATABLE, VIEW_DEFINITION
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 'v1_%';

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_view.cc	revid:mattias.jonsson@stripped
@@ -448,6 +448,7 @@ bool mysql_create_view(THD *thd, TABLE_L
     goto err;
   }
 
+  /* Not required to lock any tables. */
   if (open_query_tables(thd))
   {
     view= lex->unlink_first_table(&link_to_local);
@@ -574,6 +575,8 @@ bool mysql_create_view(THD *thd, TABLE_L
     goto err;
   }
 
+#if 0
+  /* TODO: Is it really neccesary to lock the tables? */
   lex->link_first_table_back(view, link_to_local);
   if (lock_query_tables(thd))
   {
@@ -582,6 +585,7 @@ bool mysql_create_view(THD *thd, TABLE_L
     goto err;
   }
   view= lex->unlink_first_table(&link_to_local);
+#endif
 
   /* view list (list of view fields names) */
   if (lex->view_list.elements)

No bundle (reason: useless for push emails).