9 files changed, 143 insertions, 9 deletions
diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result
index 85ca832828d..4b7e320454e 100644
--- a/mysql-test/r/help.result
+++ b/mysql-test/r/help.result
@@ -257,3 +257,12 @@ delete from mysql.help_relation where help_keyword_id=@keyword1_id and help_topi
 delete from mysql.help_relation where help_keyword_id=@keyword2_id and help_topic_id=@topic1_id;
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic3_id;
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic4_id;
+End of 4.1 tests.
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT);
+LOCK TABLES t1 WRITE;
+HELP no_such_topic;
+name	is_it_category
+UNLOCK TABLES;
+DROP TABLE t1;
+End of 5.1 tests.
diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index 7cd223197e7..1a2099b7a94 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -68,6 +68,7 @@ ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
 delete t2 from t1,t2 where t1.a=t2.a;
 ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
 drop table t1,t2;
+End of 4.1 tests.
 drop table if exists t1;
 create table t1 (a int);
 lock table t1 write;
@@ -75,3 +76,23 @@ flush tables with read lock;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 unlock tables;
 drop table t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT);
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone READ, mysql.proc READ;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+DROP TABLE t1;
+End of 5.1 tests.
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 8c933927250..a66bdfd2862 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -292,9 +292,9 @@ call p()|
 unlock tables|
 drop procedure p|
 lock tables t1 read, mysql.proc write|
-ERROR HY000: You can't combine write-locking of system 'mysql.proc' table with other tables
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
 lock tables mysql.proc write, mysql.user write|
-ERROR HY000: You can't combine write-locking of system 'mysql.proc' table with other tables
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
 lock tables t1 read, mysql.proc read|
 unlock tables|
 lock tables mysql.proc write|
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index f7631e9657a..32db7ea7fa9 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -285,3 +285,14 @@ ldt	ldt2
 drop table t1;
 drop function f1;
 SET GLOBAL log_bin_trust_function_creators = 0;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (t TIMESTAMP);
+INSERT INTO t1 VALUES (NULL), (NULL);
+LOCK TABLES t1 WRITE;
+SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
+CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL
+0
+UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');
+UNLOCK TABLES;
+DROP TABLE t1;
+End of 5.1 tests
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 30043e066db..8013fde19eb 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -900,6 +900,7 @@ drop view v1;
 drop table t1;
 create table t1 (col1 int);
 create table t2 (col1 int);
+create table t3 (col1 datetime not null);
 create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
@@ -1004,8 +1005,8 @@ ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
 ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'.
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
-insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2));
-ERROR 23000: Column 'Use_leap_seconds' cannot be null
+insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+ERROR 23000: Column 'col1' cannot be null
 create algorithm=temptable view v4 as select * from t1;
 insert into t1 values (1),(2),(3);
 insert into t1 (col1) values ((select max(col1) from v4));
@@ -1017,7 +1018,7 @@ NULL
 3
 3
 drop view v4,v3,v2,v1;
-drop table t1,t2;
+drop table t1,t2,t3;
 create table t1 (s1 int);
 create view v1 as select * from t1;
 handler v1 open as xx;
diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test
index ff431fb4ebd..5f234d7c462 100644
--- a/mysql-test/t/help.test
+++ b/mysql-test/t/help.test
@@ -114,4 +114,25 @@ delete from mysql.help_relation where help_keyword_id=@keyword2_id and help_topi
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic3_id;
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic4_id;
 
-# End of 4.1 tests
+--echo End of 4.1 tests.
+
+#
+# Test that we can use HELP even under LOCK TABLES.  See bug#9953:
+# CONVERT_TZ requires mysql.time_zone_name to be locked.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+LOCK TABLES t1 WRITE;
+
+HELP no_such_topic;
+
+UNLOCK TABLES;
+
+DROP TABLE t1;
+
+
+--echo End of 5.1 tests.
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index fb5e45433e9..2b8b430f096 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -92,7 +92,8 @@ delete from t2 using t1,t2 where t1.a=t2.a;
 delete t2 from t1,t2 where t1.a=t2.a;
 drop table t1,t2;
 
-# End of 4.1 tests
+--echo End of 4.1 tests.
+
 
 #
 # Bug#18884 "lock table + global read lock = crash"
@@ -108,3 +109,44 @@ flush tables with read lock;
 unlock tables;
 drop table t1;
 
+
+#
+# Test LOCK TABLE on system tables.  See bug#9953: CONVERT_TZ requires
+# mysql.time_zone_name to be locked.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ;
+UNLOCK TABLES;
+
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE;
+UNLOCK TABLES;
+
+LOCK TABLES mysql.time_zone READ, mysql.proc READ;
+UNLOCK TABLES;
+
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE;
+UNLOCK TABLES;
+
+# If at least one system table is locked for WRITE, then all other
+# tables should be system tables locked also for WRITE.
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ;
+
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ;
+
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE;
+
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE;
+
+DROP TABLE t1;
+
+
+--echo End of 5.1 tests.
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 5e1f74d388f..4f70539ca8d 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -246,3 +246,31 @@ drop function f1;
 SET GLOBAL log_bin_trust_function_creators = 0;
 
 # End of 5.0 tests
+
+
+#
+# BUG#9953: CONVERT_TZ requires mysql.time_zone_name to be locked
+# BUG#19339: CONVERT_TZ(): overly aggressive in locking time_zone_name
+# table
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (t TIMESTAMP);
+INSERT INTO t1 VALUES (NULL), (NULL);
+
+LOCK TABLES t1 WRITE;
+
+# The following two queries should not return error that time zone
+# tables aren't locked.  We use IS NULL below to supress timestamp
+# result.
+SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
+UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');
+
+UNLOCK TABLES;
+
+DROP TABLE t1;
+
+
+--echo End of 5.1 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 520babafb7e..abb4a803107 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -832,6 +832,7 @@ drop table t1;
 #
 create table t1 (col1 int);
 create table t2 (col1 int);
+create table t3 (col1 datetime not null);
 create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
@@ -938,7 +939,7 @@ insert into v3 (col1) values ((select max(col1) from v2));
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
 -- error 1048
-insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
 # temporary table algorithm view should be equal to subquery in the from clause
 create algorithm=temptable view v4 as select * from t1;
 insert into t1 values (1),(2),(3);
@@ -946,7 +947,7 @@ insert into t1 (col1) values ((select max(col1) from v4));
 select * from t1;
 
 drop view v4,v3,v2,v1;
-drop table t1,t2;
+drop table t1,t2,t3;
 
 #
 # HANDLER with VIEW