Fix test casebb-10.9-anel-MDEV-12459-temp-tables

2 files changed, 44 insertions, 37 deletions

diff --git a/mysql-test/main/information_schema_temp_table.result b/mysql-test/main/information_schema_temp_table.result
index 235aef6d070..a500b0f6ee0 100644
--- a/mysql-test/main/information_schema_temp_table.result
+++ b/mysql-test/main/information_schema_temp_table.result
@@ -6,27 +6,26 @@
 # -------------------------------
 # Test shadowing of a base table
 # -------------------------------
-connect  con1,localhost,root,,test,,;
+create database some_db;
+create user foo@localhost identified by 'bar';
+grant create temporary tables on some_db.* to foo@localhost;
+connect  con1,localhost,root,,some_db,,;
 # Test the warning is raised
 create table t(t int);
 create temporary table t(t int);
 Warnings:
 Note	1050	Table 't' already exists
-create user foo@localhost identified by 'bar';
 # Test shadowing of the base table for user with no privileges - no warning
-connect  con2,localhost,foo,bar,test,,;
+connect  con2,localhost,foo,bar,some_db,,;
 show grants for current_user();
 Grants for foo@localhost
 GRANT USAGE ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'
+GRANT CREATE TEMPORARY TABLES ON `some_db`.* TO `foo`@`localhost`
 show tables;
-Tables_in_test
-t
-create temporary table t(t int);
-Warnings:
-Note	1050	Table 't' already exists
+Tables_in_some_db
+create temporary table some_db.t(t int);
 show warnings;
 Level	Code	Message
-Note	1050	Table 't' already exists
 show create table t;
 Table	Create Table
 t	CREATE TEMPORARY TABLE `t` (
@@ -37,12 +36,13 @@ disconnect con2;
 connection con1;
 grant all privileges on *.* to foo@localhost with grant option;
 # Test shadowing of the base table for user that has valid privileges - warning
-connect  con2,localhost,foo,bar,test,,;
+connect  con2,localhost,foo,bar,some_db,,;
 show grants for current_user();
 Grants for foo@localhost
 GRANT ALL PRIVILEGES ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' WITH GRANT OPTION
+GRANT CREATE TEMPORARY TABLES ON `some_db`.* TO `foo`@`localhost`
 show tables;
-Tables_in_test
+Tables_in_some_db
 t
 create temporary table t(t int);
 Warnings:
@@ -60,6 +60,7 @@ connection con1;
 drop table t;
 drop table t;
 drop user foo@localhost;
+disconnect con1;
 # ------------------------
 # IS.tables tests
 # ------------------------
@@ -92,17 +93,13 @@ temporary	Y
 table_schema	test
 table_name	t_temp
 temporary	Y
-connection con1;
+connect  con1,localhost,root,,my_db,,;
 # Create the temporary table with the same name in new connection
-create temporary table test.t_temp(t int);
-Warnings:
-Level	Note
-Code	1050
-Message	Table 't_temp' already exists
+create temporary table t_temp(t int);
 insert into t_temp values (4),(5),(6), (7);
 # Show results
 select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
-table_schema	test
+table_schema	my_db
 table_name	t_temp
 temporary	Y
 connection default;
@@ -116,8 +113,9 @@ table_name	t_temp
 temporary	Y
 # Create a warning on `Create` statement if the new temporary table
 # shadows base table - note there is no warning vice versa.
-create table test.my_t (t int);
-create temporary table test.my_t (t int);
+create table some_db.my_t (t int);
+show warnings;
+create temporary table some_db.my_t (t int);
 Warnings:
 Level	Note
 Code	1050
@@ -128,7 +126,7 @@ Code	1050
 Message	Table 'my_t' already exists
 # Show results
 select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
-table_schema	test
+table_schema	some_db
 table_name	my_t
 temporary	Y
 table_schema	my_db
@@ -137,9 +135,10 @@ temporary	Y
 table_schema	test
 table_name	t_temp
 temporary	Y
-drop table test.my_t;
-drop table test.my_t;
+drop table some_db.my_t;
+drop table some_db.my_t;
 disconnect con1;
 drop table test.t_temp;
 drop table test.t_temp;
 drop database my_db;
+drop database some_db;
diff --git a/mysql-test/main/information_schema_temp_table.test b/mysql-test/main/information_schema_temp_table.test
index 7c2abb64658..78b64da7078 100644
--- a/mysql-test/main/information_schema_temp_table.test
+++ b/mysql-test/main/information_schema_temp_table.test
@@ -12,22 +12,25 @@
 --echo # Test shadowing of a base table
 --echo # -------------------------------
 
-connect (con1,localhost,root,,test,,);
+create database some_db;
+# Create user without any privileges
+create user foo@localhost identified by 'bar';
+grant create temporary tables on some_db.* to foo@localhost;
+
+connect (con1,localhost,root,,some_db,,);
+
 --echo # Test the warning is raised
 create table t(t int);
 # This should give warning, since the user can see the table and the name of a
 # temporary table in the same connection is shadowing the base table name
 create temporary table t(t int);
 
-# Create user without any privileges
-create user foo@localhost identified by 'bar';
-
 --echo # Test shadowing of the base table for user with no privileges - no warning
-connect (con2,localhost,foo,bar,test,,);
+connect (con2,localhost,foo,bar,some_db,,);
 show grants for current_user();
 show tables;
 # User has no grants and cannot see `test.t` and below shouldn't raise any warning
-create temporary table t(t int);
+create temporary table some_db.t(t int);
 show warnings;
 show create table t;
 disconnect con2;
@@ -37,7 +40,7 @@ connection con1;
 grant all privileges on *.* to foo@localhost with grant option;
 
 --echo # Test shadowing of the base table for user that has valid privileges - warning
-connect (con2,localhost,foo,bar,test,,);
+connect (con2,localhost,foo,bar,some_db,,);
 show grants for current_user();
 show tables;
 # The same test as above, but now the user has grants and should see the warning
@@ -51,6 +54,7 @@ connection con1;
 drop table t;
 drop table t;
 drop user foo@localhost;
+disconnect con1;
 
 --echo # ------------------------
 --echo # IS.tables tests
@@ -82,10 +86,10 @@ insert into my_db.t_temp values (-2),(-1);
 --vertical_results
 select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
 
-connection con1;
+connect (con1,localhost,root,,my_db,,);
 
 --echo # Create the temporary table with the same name in new connection
-create temporary table test.t_temp(t int);
+create temporary table t_temp(t int);
 insert into t_temp values (4),(5),(6), (7);
 --echo # Show results
 --vertical_results
@@ -99,8 +103,9 @@ select table_schema, table_name, temporary from  information_schema.tables where
 
 --echo # Create a warning on `Create` statement if the new temporary table
 --echo # shadows base table - note there is no warning vice versa.
-create table test.my_t (t int);
-create temporary table test.my_t (t int);
+create table some_db.my_t (t int);
+show warnings;
+create temporary table some_db.my_t (t int);
 show warnings;
 
 --echo # Show results
@@ -108,15 +113,18 @@ show warnings;
 select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
 
 # First we are removing temporary table and after base table
-drop table test.my_t;
-drop table test.my_t;
+drop table some_db.my_t;
+drop table some_db.my_t;
 
 disconnect con1;
 
-# First we are removing temporary table and after base table
+# Drop both temporary and "real" table from test.
 drop table test.t_temp;
 drop table test.t_temp;
+
 drop database my_db;
+drop database some_db;
+
 
 # Wait till all disconnects are completed
 --source include/wait_until_count_sessions.inc