MDEV-12459: The information_schema tables for getting temporary tables info

- Added list of temporary tables for current connection in `IS.tables`
where `table_type="temporary"`
- Generated warning in case temporary table shadows base table with the
same name
After Monty's review:
- Fixed indentation
- Removed duplicate "Table already exists'" warnings
- Fixed that one also gets the warning for locked tables
- Fixed failures in pr_statement_performance_analyzer and pr_table_exists:
- Updated sys_schema/table_exists to use new information_schema data

Reviewed by: monty@mariadb.org

49 files changed, 526 insertions, 42 deletions

diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result
index acef8b73074..09f145df00e 100644
--- a/mysql-test/main/create.result
+++ b/mysql-test/main/create.result
@@ -360,6 +360,8 @@ select @@warning_count;
 @@warning_count
 1
 create temporary table t3 like t2;
+Warnings:
+Note	1050	Table 't3' already exists
 show create table t3;
 Table	Create Table
 t3	CREATE TEMPORARY TABLE `t3` (
@@ -1322,6 +1324,8 @@ CREATE TEMPORARY TABLE t2 (a INT);
 CREATE VIEW t2 AS SELECT 1;
 CREATE TABLE t3 (a INT);
 CREATE TEMPORARY TABLE t3 SELECT 1;
+Warnings:
+Note	1050	Table 't3' already exists
 CREATE TEMPORARY TABLE t4 (a INT);
 CREATE TABLE t4 AS SELECT 1;
 DROP TEMPORARY TABLE t1, t2, t3, t4;
@@ -1520,6 +1524,8 @@ drop table t1;
 #
 create table t1 (a int) select 1 as a;
 create temporary table if not exists t1 select 2 as a;
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a
 2
@@ -1563,6 +1569,8 @@ select * from t2;
 a
 1
 create temporary table if not exists t1 (a int) select 3 as a;
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a
 3
@@ -1611,6 +1619,8 @@ select * from t2;
 a
 1
 create temporary table if not exists t1 (a int) select 3 as a;
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a
 3
@@ -1657,6 +1667,8 @@ select * from t1;
 a
 1
 create temporary table if not exists t1 (a int) select 3 as a;
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a
 3
@@ -1674,6 +1686,8 @@ drop view t1;
 #
 create table t1 (a int) select 1 as a;
 create temporary table if not exists t1 (a int) select * from t1;
+Warnings:
+Note	1050	Table 't1' already exists
 create temporary table if not exists t1 (a int) select * from t1;
 Warnings:
 Note	1050	Table 't1' already exists
diff --git a/mysql-test/main/create_or_replace.result b/mysql-test/main/create_or_replace.result
index 294b0623fc1..5bf35413f64 100644
--- a/mysql-test/main/create_or_replace.result
+++ b/mysql-test/main/create_or_replace.result
@@ -25,7 +25,11 @@ DROP TEMPORARY TABLE t1;
 #
 CREATE OR REPLACE TABLE t1 (a int);
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
+Warnings:
+Note	1050	Table 't1' already exists
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TEMPORARY TABLE `t1` (
@@ -51,10 +55,20 @@ drop table t1;
 CREATE OR REPLACE TABLE t1 (a int);
 LOCK TABLES t1 write;
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb;
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb;
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam;
+Warnings:
+Note	1050	Table 't1' already exists
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TEMPORARY TABLE `t1` (
diff --git a/mysql-test/main/drop.result b/mysql-test/main/drop.result
index 73beabc35fa..400cc2b2bf6 100644
--- a/mysql-test/main/drop.result
+++ b/mysql-test/main/drop.result
@@ -7,6 +7,8 @@ ERROR 42S02: Unknown table 'test.t1'
 create table t1(n int);
 insert into t1 values(1);
 create temporary table t1( n int);
+Warnings:
+Note	1050	Table 't1' already exists
 insert into t1 values(2);
 create table t1(n int);
 ERROR 42S01: Table 't1' already exists
diff --git a/mysql-test/main/flush.result b/mysql-test/main/flush.result
index 584e79e72db..882e1af7413 100644
--- a/mysql-test/main/flush.result
+++ b/mysql-test/main/flush.result
@@ -303,6 +303,8 @@ unlock tables;
 flush table v3 with read lock;
 unlock tables;
 create temporary table v1 (a int);
+Warnings:
+Note	1050	Table 'v1' already exists
 flush table v1 with read lock;
 unlock tables;
 drop view v1;
diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result
index 17a1a114575..df23514154d 100644
--- a/mysql-test/main/grant.result
+++ b/mysql-test/main/grant.result
@@ -1425,6 +1425,8 @@ DROP TABLE t1;
 #  Bug#33275 Server crash when creating temporary table mysql.user
 #
 CREATE TEMPORARY TABLE mysql.user (id INT);
+Warnings:
+Note	1050	Table 'user' already exists
 FLUSH PRIVILEGES;
 DROP TABLE mysql.user;
 drop table if exists test;
diff --git a/mysql-test/main/information_schema_temp_table.result b/mysql-test/main/information_schema_temp_table.result
new file mode 100644
index 00000000000..7ce68579bce
--- /dev/null
+++ b/mysql-test/main/information_schema_temp_table.result
@@ -0,0 +1,145 @@
+#
+# MDEV-12459: The information_schema tables for getting temporary tables 
+#             info is missing, at least for innodb, there is no 
+#             INNODB_TEMP_TABLE_INFO
+#
+# -------------------------------
+# Test shadowing of a base table
+# -------------------------------
+connect  con1,localhost,root,,test,,;
+# 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,,;
+show grants for current_user();
+Grants for foo@localhost
+GRANT USAGE ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'
+show tables;
+Tables_in_test
+t
+create temporary table t(t int);
+Warnings:
+Note	1050	Table 't' already exists
+show warnings;
+Level	Code	Message
+Note	1050	Table 't' already exists
+show create table t;
+Table	Create Table
+t	CREATE TEMPORARY TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+disconnect con2;
+# Give the privileges to the user
+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,,;
+show grants for current_user();
+Grants for foo@localhost
+GRANT ALL PRIVILEGES ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' WITH GRANT OPTION
+show tables;
+Tables_in_test
+t
+create temporary table t(t int);
+Warnings:
+Note	1050	Table 't' already exists
+show create table t;
+Table	Create Table
+t	CREATE TEMPORARY TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show warnings;
+Level	Code	Message
+disconnect con2;
+# Clean the user
+connection con1;
+drop table t;
+drop table t;
+drop user foo@localhost;
+# ------------------------
+# IS.tables tests
+# ------------------------
+connection default;
+# Create first temporary table
+create temporary table test.t_temp(t int);
+insert into t_temp values (1),(2), (3);
+# Show results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+table_schema	test
+table_name	t_temp
+temporary	Y
+# Create the base table with the same name (both should be visible)
+create table test.t_temp(t int);
+insert into t_temp values (-1),(-2);
+# Show results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+table_schema	test
+table_name	t_temp
+temporary	Y
+create database my_db;
+# Create the temporary table with the same name in new DB
+create temporary table my_db.t_temp (t int);
+insert into my_db.t_temp values (-2),(-1);
+# Show results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+table_schema	my_db
+table_name	t_temp
+temporary	Y
+table_schema	test
+table_name	t_temp
+temporary	Y
+connection con1;
+# 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
+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_name	t_temp
+temporary	Y
+connection default;
+# Show results in default connection
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+table_schema	my_db
+table_name	t_temp
+temporary	Y
+table_schema	test
+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);
+Warnings:
+Level	Note
+Code	1050
+Message	Table 'my_t' already exists
+show warnings;
+Level	Note
+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_name	my_t
+temporary	Y
+table_schema	my_db
+table_name	t_temp
+temporary	Y
+table_schema	test
+table_name	t_temp
+temporary	Y
+drop table test.my_t;
+drop table test.my_t;
+disconnect con1;
+drop table test.t_temp;
+drop table test.t_temp;
+drop database my_db;
diff --git a/mysql-test/main/information_schema_temp_table.test b/mysql-test/main/information_schema_temp_table.test
new file mode 100644
index 00000000000..b14081c7176
--- /dev/null
+++ b/mysql-test/main/information_schema_temp_table.test
@@ -0,0 +1,123 @@
+--echo #
+--echo # MDEV-12459: The information_schema tables for getting temporary tables 
+--echo #             info is missing, at least for innodb, there is no 
+--echo #             INNODB_TEMP_TABLE_INFO
+--echo #
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+--source include/have_innodb.inc
+
+--echo # -------------------------------
+--echo # Test shadowing of a base table
+--echo # -------------------------------
+
+connect (con1,localhost,root,,test,,);
+--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,,);
+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);
+show warnings;
+show create table t;
+disconnect con2;
+
+--echo # Give the privileges to the user
+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,,);
+show grants for current_user();
+show tables;
+# The same test as above, but now the user has grants and should see the warning
+create temporary table t(t int);
+show create table t;
+show warnings;
+disconnect con2;
+
+--echo # Clean the user
+connection con1;
+drop table t;
+drop table t;
+drop user foo@localhost;
+
+--echo # ------------------------
+--echo # IS.tables tests
+--echo # ------------------------
+connection default;
+
+--echo # Create first temporary table
+create temporary table test.t_temp(t int);
+insert into t_temp values (1),(2), (3);
+
+--echo # Show results
+--vertical_results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+
+--echo # Create the base table with the same name (both should be visible)
+# Create the base table with the same name as temporary table.
+create table test.t_temp(t int);
+insert into t_temp values (-1),(-2);
+
+--echo # Show results
+--vertical_results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+
+create database my_db;
+--echo # Create the temporary table with the same name in new DB
+create temporary table my_db.t_temp (t int);
+insert into my_db.t_temp values (-2),(-1);
+--echo # Show results
+--vertical_results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+
+connection con1;
+
+--echo # Create the temporary table with the same name in new connection
+create temporary table test.t_temp(t int);
+insert into t_temp values (4),(5),(6), (7);
+--echo # Show results
+--vertical_results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+
+connection default;
+
+--echo # Show results in default connection
+--vertical_results
+select table_schema, table_name, temporary from  information_schema.tables where table_type='temporary';
+
+--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);
+show warnings;
+
+--echo # Show results
+--vertical_results
+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;
+
+disconnect con1;
+
+# First we are removing temporary table and after base table
+drop table test.t_temp;
+drop table test.t_temp;
+drop database my_db;
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
+
diff --git a/mysql-test/main/lock.result b/mysql-test/main/lock.result
index d0adc923563..8402e7099f6 100644
--- a/mysql-test/main/lock.result
+++ b/mysql-test/main/lock.result
@@ -216,6 +216,8 @@ create table t1 (a int);
 create table t2 (a int);
 lock table t1 write, t2 write;
 create temporary table t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 flush table t1;
 drop temporary table t1;
 select * from t1;
@@ -527,6 +529,8 @@ DROP TABLE t1;
 CREATE TABLE t (c INT);
 LOCK TABLES t READ LOCAL;
 CREATE TEMPORARY TABLE t (a INT) SELECT 1 AS a;
+Warnings:
+Note	1050	Table 't' already exists
 DROP SEQUENCE t;
 ERROR 42S02: Unknown SEQUENCE: 'test.t'
 DROP TEMPORARY TABLE t;
diff --git a/mysql-test/main/lock_multi.result b/mysql-test/main/lock_multi.result
index 30cd63e60ed..6d0a1807d6e 100644
--- a/mysql-test/main/lock_multi.result
+++ b/mysql-test/main/lock_multi.result
@@ -608,7 +608,11 @@ CREATE TABLE t1 (id INT);
 LOCK TABLE t1 WRITE;
 connect  con1, localhost, root;
 CREATE TEMPORARY TABLE t1 (id INT);
+Warnings:
+Note	1050	Table 't1' already exists
 ALTER TABLE t1 ADD COLUMN j INT;
+Warnings:
+Note	1050	Table 't1' already exists
 connection default;
 disconnect con1;
 UNLOCK TABLES;
diff --git a/mysql-test/main/merge.result b/mysql-test/main/merge.result
index d1950d03939..ef439cf66fa 100644
--- a/mysql-test/main/merge.result
+++ b/mysql-test/main/merge.result
@@ -3056,6 +3056,8 @@ c1	c2
 213	223
 #
 CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (611, 621);
 SELECT * FROM m1;
 c1	c2
@@ -3463,6 +3465,8 @@ c1	c2
 213	223
 #
 CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (611, 621);
 SELECT * FROM m1;
 c1	c2
diff --git a/mysql-test/main/ps_ddl.result b/mysql-test/main/ps_ddl.result
index d99aeb0fa54..12ff17a5b14 100644
--- a/mysql-test/main/ps_ddl.result
+++ b/mysql-test/main/ps_ddl.result
@@ -511,6 +511,8 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 create temporary table t1 AS SELECT 1;
+Warnings:
+Note	1050	Table 't1' already exists
 execute stmt;
 count(*)
 1
@@ -571,6 +573,8 @@ Part 11: TEMPORARY TABLE -> TABLE transitions
 create table t1 (a int);
 insert into t1 (a) value (1);
 create temporary table t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 prepare stmt from "select * from t1";
 execute stmt;
 a
@@ -593,6 +597,8 @@ deallocate prepare stmt;
 #            temporary table disappears
 create table t1 (a int);
 create temporary table t1 as select 1 as a;
+Warnings:
+Note	1050	Table 't1' already exists
 prepare stmt from "select count(*) from t1";
 execute stmt;
 count(*)
@@ -722,6 +728,8 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 create temporary table t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 execute stmt;
 a
 1
@@ -751,6 +759,8 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 create temporary table t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 execute stmt;
 a
 1
@@ -779,6 +789,8 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 create temporary table t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 execute stmt;
 table_name
 t1
@@ -2029,8 +2041,12 @@ create table t1 (a int);
 insert into t1 (a) values (1);
 prepare stmt from "create temporary table if not exists t2 as select * from t1";
 execute stmt;
+Warnings:
+Note	1050	Table 't2' already exists
 drop table t2;
 execute stmt;
+Warnings:
+Note	1050	Table 't2' already exists
 call p_verify_reprepare_count(0);
 SUCCESS
 
@@ -2054,6 +2070,8 @@ a
 1
 drop table t2;
 create temporary table t2 (a varchar(10));
+Warnings:
+Note	1050	Table 't2' already exists
 execute stmt;
 Warnings:
 Note	1050	Table 't2' already exists
@@ -2543,6 +2561,8 @@ PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)';
 EXECUTE stmt1;
 # Create temporary table which will shadow t1.
 CREATE TEMPORARY TABLE t1 (b int);
+Warnings:
+Note	1050	Table 't1' already exists
 # The below execution of statement should not fail with ER_CANT_REOPEN
 # error. Instead stmt1 should be auto-matically reprepared and succeed.
 EXECUTE stmt1;
diff --git a/mysql-test/main/query_cache.result b/mysql-test/main/query_cache.result
index 3a81b648171..dd1a9f92a21 100644
--- a/mysql-test/main/query_cache.result
+++ b/mysql-test/main/query_cache.result
@@ -752,6 +752,8 @@ id
 3
 create temporary table t1 (a int not null auto_increment
 primary key);
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a
 drop table t1;
diff --git a/mysql-test/main/read_only.result b/mysql-test/main/read_only.result
index 83dfada5f29..84568e2ca61 100644
--- a/mysql-test/main/read_only.result
+++ b/mysql-test/main/read_only.result
@@ -45,6 +45,8 @@ ERROR HY000: The MariaDB server is running with the --read-only option so it can
 delete t3 from t1,t3 where t1.a=t3.a;
 delete t4 from t3,t4 where t4.a=t3.a;
 create temporary table t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
 insert into t1 values(1);
 update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
 delete t1 from t1,t3 where t1.a=t3.a;
diff --git a/mysql-test/main/read_only_innodb.result b/mysql-test/main/read_only_innodb.result
index abfc5322ed0..89037b8275d 100644
--- a/mysql-test/main/read_only_innodb.result
+++ b/mysql-test/main/read_only_innodb.result
@@ -143,6 +143,8 @@ UNLOCK TABLES;
 # Lock temporary table that shadows a base table:
 
 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
+Warnings:
+Note	1050	Table 't1' already exists
 LOCK TABLES t1 WRITE;
 DROP TABLE t1;
 SELECT * FROM t1;
diff --git a/mysql-test/main/rename.result b/mysql-test/main/rename.result
index 15c744e158c..ea370676d76 100644
--- a/mysql-test/main/rename.result
+++ b/mysql-test/main/rename.result
@@ -135,6 +135,8 @@ drop table t2;
 CREATE TABLE t1 (a INT);
 insert into t1 values (1);
 CREATE TEMPORARY TABLE t1 (b INT);
+Warnings:
+Note	1050	Table 't1' already exists
 insert into t1 values (2);
 RENAME TABLE t1 TO tmp, t1 TO t2;
 select * from tmp;
diff --git a/mysql-test/main/reopen_temp_table.result b/mysql-test/main/reopen_temp_table.result
index ef215366db7..bedcec74a35 100644
--- a/mysql-test/main/reopen_temp_table.result
+++ b/mysql-test/main/reopen_temp_table.result
@@ -94,6 +94,8 @@ CREATE TABLE t1(i INT);
 INSERT INTO t1 VALUES(1), (2);
 CREATE TEMPORARY TABLE t1
 SELECT temp_1.i a, temp_2.i b FROM t1 AS temp_1, t1 AS temp_2;
+Warnings:
+Note	1050	Table 't1' already exists
 SELECT * FROM t1;
 a	b
 1	1
diff --git a/mysql-test/main/sp-anchor-row-type-cursor.result b/mysql-test/main/sp-anchor-row-type-cursor.result
index 64359988a3d..367ce604b5c 100644
--- a/mysql-test/main/sp-anchor-row-type-cursor.result
+++ b/mysql-test/main/sp-anchor-row-type-cursor.result
@@ -760,6 +760,8 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (a INT, b VARCHAR(10));
 CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE PROCEDURE p1()
 BEGIN
 DECLARE cur CURSOR FOR SELECT * FROM t1;
diff --git a/mysql-test/main/sp-anchor-row-type-table.result b/mysql-test/main/sp-anchor-row-type-table.result
index b51ee5bf55d..b3510adcd46 100644
--- a/mysql-test/main/sp-anchor-row-type-table.result
+++ b/mysql-test/main/sp-anchor-row-type-table.result
@@ -562,6 +562,8 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (a INT, b VARCHAR(10));
 CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE PROCEDURE p1()
 BEGIN
 DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb');
diff --git a/mysql-test/main/sp-anchor-type.result b/mysql-test/main/sp-anchor-type.result
index ac867bd5ca3..7e19232070b 100644
--- a/mysql-test/main/sp-anchor-type.result
+++ b/mysql-test/main/sp-anchor-type.result
@@ -161,6 +161,8 @@ DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(10));
 INSERT INTO t1 VALUES ('t1');
 CREATE TEMPORARY TABLE t1 (a INT);
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (10);
 SELECT * FROM t1;
 a
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index a7faeaf2f0d..0452c14d404 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -7179,6 +7179,8 @@ CREATE VIEW t1 AS SELECT 10 AS f1;
 CALL p1(1);
 ERROR HY000: The target table t1 of the INSERT is not insertable-into
 CREATE TEMPORARY TABLE t1 (f1 INT);
+Warnings:
+Note	1050	Table 't1' already exists
 # t1 still refers to the view since it was inlined
 CALL p1(2);
 ERROR HY000: The target table t1 of the INSERT is not insertable-into
@@ -7199,6 +7201,8 @@ CREATE VIEW v2_unrelated AS SELECT 1 AS r1;
 CALL p1(4);
 ERROR HY000: The target table t1 of the INSERT is not insertable-into
 CREATE TEMPORARY TABLE t1 (f1 int);
+Warnings:
+Note	1050	Table 't1' already exists
 ALTER VIEW v2_unrelated AS SELECT 2 AS r1;
 # Alter view causes the sp cache to be invalidated.
 # Now t1 refers to the temporary table, not the view.
diff --git a/mysql-test/main/temp_table.result b/mysql-test/main/temp_table.result
index 0e443cb71dd..97ef065dcc6 100644
--- a/mysql-test/main/temp_table.result
+++ b/mysql-test/main/temp_table.result
@@ -3,6 +3,8 @@
 #
 create table t1 (a int);
 create temporary table t1 AS SELECT 1;
+Warnings:
+Note	1050	Table 't1' already exists
 create temporary table t1 AS SELECT 1;
 ERROR 42S01: Table 't1' already exists
 create temporary table t1 (a int);
@@ -21,6 +23,8 @@ drop temporary table t1;
 CREATE TABLE t1 (c int not null, d char (10) not null);
 insert into t1 values(1,""),(2,"a"),(3,"b");
 CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null);
+Warnings:
+Note	1050	Table 't1' already exists
 insert into t1 values(4,"e"),(5,"f"),(6,"g");
 alter table t1 rename t2;
 select * from t1;
@@ -41,6 +45,8 @@ a	b
 5	f
 6	g
 create TEMPORARY TABLE t2 engine=heap select * from t1;
+Warnings:
+Note	1050	Table 't2' already exists
 create TEMPORARY TABLE IF NOT EXISTS t2 (a int) engine=heap;
 Warnings:
 Note	1050	Table 't2' already exists
@@ -54,6 +60,8 @@ a	b
 5	f
 6	g
 alter table t2 add primary key (a,b);
+Warnings:
+Note	1050	Table 't2' already exists
 drop table t1,t2;
 select * from t1;
 c	d
@@ -62,6 +70,8 @@ c	d
 3	b
 drop table t2;
 create temporary table t1 select *,2 as "e" from t1;
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 c	d	e
 1		2
@@ -158,6 +168,8 @@ CREATE TABLE t1 (i INT);
 LOCK TABLE t1 WRITE;
 connect  conn1, localhost, root,,;
 CREATE TEMPORARY TABLE t1 (i INT);
+Warnings:
+Note	1050	Table 't1' already exists
 The following command should not block
 DROP TEMPORARY TABLE t1;
 disconnect conn1;
@@ -241,6 +253,8 @@ CREATE DATABASE bug48067;
 CREATE TABLE bug48067.t1 (c1 int);
 INSERT INTO bug48067.t1 values (1);
 CREATE TEMPORARY TABLE bug48067.t1 (c1 int);
+Warnings:
+Note	1050	Table 't1' already exists
 DROP DATABASE bug48067;
 DROP TEMPORARY table bug48067.t1;
 End of 5.1 tests
@@ -347,6 +361,8 @@ ERROR 42S02: Table 'temp_db.temp_t1' doesn't exist
 CREATE TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
 INSERT INTO t1 VALUES("BASE TABLE");
 CREATE TEMPORARY TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES("TEMPORARY TABLE");
 SELECT * FROM t1;
 c1
@@ -365,6 +381,8 @@ ERROR 42S02: Table 'temp_db.t1' doesn't exist
 CREATE TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
 INSERT INTO t1 VALUES("BASE TABLE");
 CREATE TEMPORARY TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES("TEMPORARY TABLE");
 SELECT * FROM t1;
 c1
@@ -395,6 +413,8 @@ DROP TEMPORARY TABLE temp_t1;
 USE temp_db;
 CREATE TABLE t1(i INT)ENGINE=INNODB;
 CREATE TEMPORARY TABLE t1(i INT) ENGINE=INNODB;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (1);
 DROP DATABASE temp_db;
 CREATE DATABASE temp_db;
diff --git a/mysql-test/main/trigger.result b/mysql-test/main/trigger.result
index f031dec2e1a..10efab44f2f 100644
--- a/mysql-test/main/trigger.result
+++ b/mysql-test/main/trigger.result
@@ -2232,6 +2232,8 @@ create table t1 (i int);
 create trigger t1_bi before insert on t1 for each row set @a:=1;
 # Create temporary table which shadows base table with trigger.
 create temporary table t1 (j int);
+Warnings:
+Note	1050	Table 't1' already exists
 # Dropping of trigger should succeed.
 drop trigger t1_bi;
 select trigger_name from information_schema.triggers
diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result
index c0a45a2ac14..396546eec67 100644
--- a/mysql-test/main/union.result
+++ b/mysql-test/main/union.result
@@ -490,6 +490,8 @@ a
 (SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
 ERROR 42000: Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'
 create temporary table t1 select a from t1 union select a from t2;
+Warnings:
+Note	1050	Table 't1' already exists
 drop temporary table t1;
 create table t1 select a from t1 union select a from t2;
 ERROR 42S01: Table 't1' already exists
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 075e50e1b0a..9557b3ba147 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -24,6 +24,8 @@ select is_updatable from information_schema.views where table_name='v1';
 is_updatable
 NO
 create temporary table t1 (a int, b int);
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a	b
 select c from v1;
@@ -4716,6 +4718,8 @@ ERROR 42S02: Table 'test.t1' doesn't exist
 DROP TABLE t1;
 CREATE VIEW v1 AS SELECT 1 AS f1;
 CREATE TEMPORARY TABLE v1 (id INT);
+Warnings:
+Note	1050	Table 'v1' already exists
 ALTER VIEW v1 AS SELECT 2 AS f1;
 DROP TABLE v1;
 SELECT * FROM v1;
@@ -4730,6 +4734,8 @@ DROP TABLE IF EXISTS t1, t2;
 DROP VIEW IF EXISTS t2;
 CREATE TABLE t1 (f1 integer);
 CREATE TEMPORARY TABLE IF NOT EXISTS t1 (f1 integer);
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE TEMPORARY TABLE t2 (f1 integer);
 DROP TABLE t1;
 FLUSH TABLES WITH READ LOCK;
diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
index 31d794c9f61..beaffc0e7e5 100644
--- a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
+++ b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
@@ -809,6 +809,8 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (a INT, b VARCHAR(10));
 CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE PROCEDURE p1
 AS
 CURSOR cur IS SELECT * FROM t1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-row.result b/mysql-test/suite/compat/oracle/r/sp-row.result
index 7fd986a71c8..fcf4e2389a0 100644
--- a/mysql-test/suite/compat/oracle/r/sp-row.result
+++ b/mysql-test/suite/compat/oracle/r/sp-row.result
@@ -2788,6 +2788,8 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (a INT, b VARCHAR(10));
 CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
+Warnings:
+Note	1050	Table 't1' already exists
 CREATE PROCEDURE p1
 AS
 rec t1%ROWTYPE:=ROW(10,'bb');
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index a9a97769333..3466f442339 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -1387,6 +1387,8 @@ DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(10));
 INSERT INTO t1 VALUES ('t1');
 CREATE TEMPORARY TABLE t1 (a INT);
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (10);
 SELECT * FROM t1;
 a
diff --git a/mysql-test/suite/engines/funcs/r/rpl_row_drop.result b/mysql-test/suite/engines/funcs/r/rpl_row_drop.result
index 8753764e81e..eed538c7965 100644
--- a/mysql-test/suite/engines/funcs/r/rpl_row_drop.result
+++ b/mysql-test/suite/engines/funcs/r/rpl_row_drop.result
@@ -4,6 +4,8 @@ connection master;
 CREATE TABLE t1 (a int);
 CREATE TABLE t2 (a int);
 CREATE TEMPORARY TABLE t2 (a int, b int);
+Warnings:
+Note	1050	Table 't2' already exists
 SHOW TABLES;
 Tables_in_test
 t1
@@ -26,6 +28,8 @@ t1
 t2
 connection master;
 CREATE TEMPORARY TABLE t2 (a int, b int);
+Warnings:
+Note	1050	Table 't2' already exists
 SHOW TABLES;
 Tables_in_test
 t1
diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result
index 90d72b451b9..49f45e14824 100644
--- a/mysql-test/suite/funcs_1/r/innodb_views.result
+++ b/mysql-test/suite/funcs_1/r/innodb_views.result
@@ -4323,6 +4323,8 @@ DROP TABLE IF EXISTS t2_temp;
 DROP VIEW  IF EXISTS v1;
 Create table t1_temp(f59 char(10),f60 int) ;
 Create temporary table t1_temp(f59 char(10),f60 int) ;
+Warnings:
+Note	1050	Table 't1_temp' already exists
 Insert into t1_temp values('FER',90);
 Insert into t1_temp values('CAR',27);
 Create view v1 as select * from t1_temp ;
@@ -4340,6 +4342,8 @@ DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
 CREATE           TABLE t1 (f1 char(10));
 CREATE TEMPORARY TABLE t2 (f2 char(10));
+Warnings:
+Note	1050	Table 't2' already exists
 INSERT INTO t1 VALUES('t1');
 INSERT INTO t1 VALUES('A');
 INSERT INTO t2 VALUES('t2');
diff --git a/mysql-test/suite/funcs_1/r/is_tables.result b/mysql-test/suite/funcs_1/r/is_tables.result
index 08e721cde1c..d665313dbb3 100644
--- a/mysql-test/suite/funcs_1/r/is_tables.result
+++ b/mysql-test/suite/funcs_1/r/is_tables.result
@@ -387,6 +387,8 @@ ENGINE = <other_engine_type>
 AS SELECT 1;
 SELECT table_name, table_type FROM information_schema.tables
 WHERE table_name = 't1_my_tablex';
+table_name	t1_my_tablex
+table_type	TEMPORARY
 DROP TEMPORARY TABLE test.t1_my_tablex;
 CREATE TABLE db_datadict.t1_my_tablex
 ENGINE = <engine_type> AS
diff --git a/mysql-test/suite/funcs_1/r/is_tables_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_embedded.result
index 5e9259bf557..b0907219c77 100644
--- a/mysql-test/suite/funcs_1/r/is_tables_embedded.result
+++ b/mysql-test/suite/funcs_1/r/is_tables_embedded.result
@@ -395,6 +395,8 @@ ENGINE = <other_engine_type>
 AS SELECT 1;
 SELECT table_name, table_type FROM information_schema.tables
 WHERE table_name = 't1_my_tablex';
+table_name	t1_my_tablex
+table_type	TEMPORARY
 DROP TEMPORARY TABLE test.t1_my_tablex;
 CREATE TABLE db_datadict.t1_my_tablex
 ENGINE = <engine_type> AS
diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result
index 417c0e85188..148099390ce 100644
--- a/mysql-test/suite/funcs_1/r/memory_views.result
+++ b/mysql-test/suite/funcs_1/r/memory_views.result
@@ -4324,6 +4324,8 @@ DROP TABLE IF EXISTS t2_temp;
 DROP VIEW  IF EXISTS v1;
 Create table t1_temp(f59 char(10),f60 int) ;
 Create temporary table t1_temp(f59 char(10),f60 int) ;
+Warnings:
+Note	1050	Table 't1_temp' already exists
 Insert into t1_temp values('FER',90);
 Insert into t1_temp values('CAR',27);
 Create view v1 as select * from t1_temp ;
@@ -4341,6 +4343,8 @@ DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
 CREATE           TABLE t1 (f1 char(10));
 CREATE TEMPORARY TABLE t2 (f2 char(10));
+Warnings:
+Note	1050	Table 't2' already exists
 INSERT INTO t1 VALUES('t1');
 INSERT INTO t1 VALUES('A');
 INSERT INTO t2 VALUES('t2');
diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result
index efd5ee1c568..bdd988eae52 100644
--- a/mysql-test/suite/funcs_1/r/myisam_views-big.result
+++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result
@@ -4793,6 +4793,8 @@ DROP TABLE IF EXISTS t2_temp;
 DROP VIEW  IF EXISTS v1;
 Create table t1_temp(f59 char(10),f60 int) ;
 Create temporary table t1_temp(f59 char(10),f60 int) ;
+Warnings:
+Note	1050	Table 't1_temp' already exists
 Insert into t1_temp values('FER',90);
 Insert into t1_temp values('CAR',27);
 Create view v1 as select * from t1_temp ;
@@ -4810,6 +4812,8 @@ DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
 CREATE           TABLE t1 (f1 char(10));
 CREATE TEMPORARY TABLE t2 (f2 char(10));
+Warnings:
+Note	1050	Table 't2' already exists
 INSERT INTO t1 VALUES('t1');
 INSERT INTO t1 VALUES('A');
 INSERT INTO t2 VALUES('t2');
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result b/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result
index c45579aec2b..6acf89fd446 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result
@@ -40,6 +40,8 @@ INSERT INTO t1 VALUES(1), (2);
 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
 # restart
 CREATE TEMPORARY TABLE t2 (fld1 INT NOT NULL)ENGINE=INNODB;
+Warnings:
+Note	1050	Table 't2' already exists
 UPDATE t1 SET fld1= 3 WHERE fld1= 2;
 connect con1,localhost,root,,test;
 SELECT fld2 FROM t2;
diff --git a/mysql-test/suite/handler/interface.result b/mysql-test/suite/handler/interface.result
index 5dcb80d2b2f..2dbd4b0cddd 100644
--- a/mysql-test/suite/handler/interface.result
+++ b/mysql-test/suite/handler/interface.result
@@ -306,6 +306,8 @@ PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST';
 ERROR 42S02: Unknown table 't1' in HANDLER
 create view v as select 1;
 create temporary table v as select 2;
+Warnings:
+Note	1050	Table 'v' already exists
 handler v open;
 prepare stmt from 'create table if not exists v as select 3';
 execute stmt;
diff --git a/mysql-test/suite/maria/temporary.result b/mysql-test/suite/maria/temporary.result
index 2592e04be2b..c414d2e49b1 100644
--- a/mysql-test/suite/maria/temporary.result
+++ b/mysql-test/suite/maria/temporary.result
@@ -1,6 +1,8 @@
 CREATE TABLE t1 (b INT);
 INSERT INTO t1 VALUES (5);
 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=Aria ROW_FORMAT=DYNAMIC;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (1);
 DELETE FROM t1 LIMIT 2;
 OPTIMIZE TABLE t1;
@@ -18,6 +20,7 @@ test.t1	check	status	OK
 ALTER TABLE t1 CHANGE COLUMN IF EXISTS x x INT;
 Warnings:
 Note	1054	Unknown column 'x' in 't1'
+Note	1050	Table 't1' already exists
 ALTER TABLE t1;
 DROP TEMPORARY TABLE t1;
 DROP TABLE t1;
diff --git a/mysql-test/suite/plugins/r/audit_null.result b/mysql-test/suite/plugins/r/audit_null.result
index ada85b661ee..1a88e8d7716 100644
--- a/mysql-test/suite/plugins/r/audit_null.result
+++ b/mysql-test/suite/plugins/r/audit_null.result
@@ -41,11 +41,15 @@ t2.a+1	t2_copy.a+2
 3	4
 drop view v1;
 create temporary table t2 (a date);
+Warnings:
+Note	1050	Table 't2' already exists
 insert t2 values ('2020-10-09');
 select * from t2;
 a
 2020-10-09
 alter table t2 add column b int;
+Warnings:
+Note	1050	Table 't2' already exists
 drop table t2;
 explain select distinct * from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -98,9 +102,11 @@ root[root] @ localhost []	mysql.column_stats : read
 root[root] @ localhost []	mysql.index_stats : read
 root[root] @ localhost []	>> drop view v1
 root[root] @ localhost []	>> create temporary table t2 (a date)
+root[root] @ localhost []	>> SHOW WARNINGS
 root[root] @ localhost []	>> insert t2 values ('2020-10-09')
 root[root] @ localhost []	>> select * from t2
 root[root] @ localhost []	>> alter table t2 add column b int
+root[root] @ localhost []	>> SHOW WARNINGS
 root[root] @ localhost []	>> drop table t2
 root[root] @ localhost []	>> explain select distinct * from t2
 root[root] @ localhost []	test.t2 : read
diff --git a/mysql-test/suite/rpl/r/rename.result b/mysql-test/suite/rpl/r/rename.result
index 8220ae1f3b1..acf86964c1f 100644
--- a/mysql-test/suite/rpl/r/rename.result
+++ b/mysql-test/suite/rpl/r/rename.result
@@ -6,6 +6,8 @@ include/master-slave.inc
 #
 CREATE TABLE t1 (a INT);
 CREATE TEMPORARY TABLE t1 (b INT);
+Warnings:
+Note	1050	Table 't1' already exists
 RENAME TABLE t1 TO tmp, tmp TO t1;
 SHOW CREATE TABLE t1;
 Table	Create Table
diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result
index eff939512e2..e8e7d301f91 100644
--- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result
+++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result
@@ -575,6 +575,8 @@ include/stop_slave.inc
 connection server_1;
 INSERT INTO t1 VALUES (40, 10);
 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+Warnings:
+Note	1050	Table 't1' already exists
 INSERT INTO t1 VALUES (41);
 BEGIN;
 INSERT INTO t2 SELECT a, 20 FROM t1;
diff --git a/mysql-test/suite/rpl/r/rpl_row_drop.result b/mysql-test/suite/rpl/r/rpl_row_drop.result
index 8753764e81e..eed538c7965 100644
--- a/mysql-test/suite/rpl/r/rpl_row_drop.result
+++ b/mysql-test/suite/rpl/r/rpl_row_drop.result
@@ -4,6 +4,8 @@ connection master;
 CREATE TABLE t1 (a int);
 CREATE TABLE t2 (a int);
 CREATE TEMPORARY TABLE t2 (a int, b int);
+Warnings:
+Note	1050	Table 't2' already exists
 SHOW TABLES;
 Tables_in_test
 t1
@@ -26,6 +28,8 @@ t1
 t2
 connection master;
 CREATE TEMPORARY TABLE t2 (a int, b int);
+Warnings:
+Note	1050	Table 't2' already exists
 SHOW TABLES;
 Tables_in_test
 t1
diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result
index 6f70f335d88..7a7200bd8b8 100644
--- a/mysql-test/suite/sql_sequence/create.result
+++ b/mysql-test/suite/sql_sequence/create.result
@@ -486,6 +486,8 @@ select previous value for t1;
 previous value for t1
 1
 CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10;
+Warnings:
+Note	1050	Table 't1' already exists
 select previous value for t1;
 previous value for t1
 NULL
@@ -515,6 +517,8 @@ select previous value for t1;
 previous value for t1
 1
 CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb;
+Warnings:
+Note	1050	Table 't1' already exists
 START TRANSACTION WITH CONSISTENT SNAPSHOT;
 select previous value for t1;
 previous value for t1
@@ -610,6 +614,8 @@ drop table s,s2;
 #
 create or replace sequence s;
 create temporary table s (i int);
+Warnings:
+Note	1050	Table 's' already exists
 drop sequence s;
 show create table s;
 Table	Create Table
@@ -619,6 +625,8 @@ s	CREATE TEMPORARY TABLE `s` (
 drop table s;
 create or replace sequence s;
 create temporary sequence s;
+Warnings:
+Note	1050	Table 's' already exists
 show create table s;
 Table	Create Table
 s	CREATE TEMPORARY TABLE `s` (
@@ -647,6 +655,8 @@ s	CREATE TABLE `s` (
 drop table s;
 create or replace sequence s;
 create temporary sequence s;
+Warnings:
+Note	1050	Table 's' already exists
 drop temporary sequence s;
 show create table s;
 Table	Create Table
diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result
index 5226ce78789..0540b9c8203 100644
--- a/mysql-test/suite/sql_sequence/other.result
+++ b/mysql-test/suite/sql_sequence/other.result
@@ -332,15 +332,21 @@ DROP TEMPORARY SEQUENCE s;
 ERROR 42S02: Unknown SEQUENCE: 'test.s'
 UNLOCK TABLES;
 CREATE TEMPORARY SEQUENCE s;
+Warnings:
+Note	1050	Table 's' already exists
 LOCK TABLE t WRITE;
 DROP TEMPORARY SEQUENCE s;
 UNLOCK TABLES;
 DROP SEQUENCE s;
 create table s(a INT);
 CREATE TEMPORARY TABLE s (f INT);
+Warnings:
+Note	1050	Table 's' already exists
 LOCK TABLE t WRITE;
 DROP TEMPORARY TABLE s;
 CREATE TEMPORARY TABLE s (f INT);
+Warnings:
+Note	1050	Table 's' already exists
 DROP TABLE s;
 DROP TABLE s;
 ERROR HY000: Table 's' was not locked with LOCK TABLES
diff --git a/mysql-test/suite/sysschema/r/pr_table_exists.result b/mysql-test/suite/sysschema/r/pr_table_exists.result
index 459ad1639e7..bfc7292055e 100644
--- a/mysql-test/suite/sysschema/r/pr_table_exists.result
+++ b/mysql-test/suite/sysschema/r/pr_table_exists.result
@@ -11,6 +11,8 @@ SELECT @exists;
 @exists
 VIEW
 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
+Warnings:
+Note	1050	Table 't1' already exists
 CALL sys.table_exists('test', 't1', @exists);
 SELECT @exists;
 @exists
diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result
index 0cdc5945acf..c4dab0cb1cf 100644
--- a/mysql-test/suite/versioning/r/alter.result
+++ b/mysql-test/suite/versioning/r/alter.result
@@ -449,6 +449,8 @@ delete from t1;
 alter table t1 engine=myisam;
 # MDEV-14692 crash in MDL_context::upgrade_shared_lock()
 create or replace temporary table t (a int);
+Warnings:
+Note	1050	Table 't' already exists
 alter table t change column if exists b c bigint unsigned generated always as row start;
 ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE
 alter table t change column if exists b c bigint unsigned generated always as row end;
diff --git a/scripts/sys_schema/procedures/table_exists.sql b/scripts/sys_schema/procedures/table_exists.sql
index e06d018308f..2a80500ba6a 100644
--- a/scripts/sys_schema/procedures/table_exists.sql
+++ b/scripts/sys_schema/procedures/table_exists.sql
@@ -114,36 +114,14 @@ BEGIN
     DECLARE CONTINUE HANDLER FOR 1050 SET v_error = TRUE;
     DECLARE CONTINUE HANDLER FOR 1146 SET v_error = TRUE;
 
-    SET out_exists = '';
-
-    -- Verify whether the table name exists as a normal table
-    IF (EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table)) THEN
-        -- Unfortunately the only way to determine whether there is also a temporary table is to try to create
-        -- a temporary table with the same name. If it succeeds the table didn't exist as a temporary table.
-        SET @sys.tmp.table_exists.SQL = CONCAT('CREATE TEMPORARY TABLE `', in_db, '`.`', in_table, '` (id INT PRIMARY KEY)');
-        PREPARE stmt_create_table FROM @sys.tmp.table_exists.SQL;
-        EXECUTE stmt_create_table;
-        DEALLOCATE PREPARE stmt_create_table;
-        IF (v_error) THEN
-            SET out_exists = 'TEMPORARY';
-        ELSE
-            -- The temporary table was created, i.e. it didn't exist. Remove it again so we don't leave garbage around.
-            SET @sys.tmp.table_exists.SQL = CONCAT('DROP TEMPORARY TABLE `', in_db, '`.`', in_table, '`');
-            PREPARE stmt_drop_table FROM @sys.tmp.table_exists.SQL;
-            EXECUTE stmt_drop_table;
-            DEALLOCATE PREPARE stmt_drop_table;
-            SET out_exists = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table);
-        END IF;
-    ELSE
-        -- Check whether a temporary table exists with the same name.
-        -- If it does it's possible to SELECT from the table without causing an error.
-        -- If it does not exist even a PREPARE using the table will fail.
-        SET @sys.tmp.table_exists.SQL = CONCAT('SELECT COUNT(*) FROM `', in_db, '`.`', in_table, '`');
-        PREPARE stmt_select FROM @sys.tmp.table_exists.SQL;
-        IF (NOT v_error) THEN
-            DEALLOCATE PREPARE stmt_select;
-            SET out_exists = 'TEMPORARY';
-        END IF;
+    SET out_exists = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table AND TABLE_TYPE= 'temporary');
+    IF out_exists is NULL
+    THEN
+        SET out_exists = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table);
+       IF out_exists is NULL
+       THEN
+           SET out_exists='';
+       END IF;
     END IF;
 END$$
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 78d367e4005..9893060693a 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4000,9 +4000,7 @@ lock_table_names(THD *thd, const DDL_options_st &options,
         table->mdl_request.type == MDL_SHARED_READ_ONLY ||
         table->open_type == OT_TEMPORARY_ONLY ||
         (table->open_type == OT_TEMPORARY_OR_BASE && is_temporary_table(table)))
-    {
       continue;
-    }
 
     /* Write lock on normal tables is not allowed in a read only transaction. */
     if (thd->tx_read_only)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index fe2307a2e91..664073cdd9c 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -8267,7 +8267,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
   ptr->select_lex= this;
   /*
     We can't cache internal temporary tables between prepares as the
-    table may be deleted before next exection.
+    table may be deleted before next execution.
  */
   ptr->cacheable_table= !table->is_derived_table();
   ptr->index_hints= index_hints_arg;
@@ -8288,8 +8288,8 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
                     !cmp(&ptr->db, &tables->db)) &&
                    !tables->sequence))
       {
-	my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str.str); /* purecov: tested */
-	DBUG_RETURN(0);				/* purecov: tested */
+        my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str.str); /* purecov: tested */
+        DBUG_RETURN(0);				/* purecov: tested */
       }
     }
   }
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index f289c1a3a02..2083c086409 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -69,6 +69,7 @@
 
 #include "lex_symbol.h"
 #define KEYWORD_SIZE 64
+#define IS_USER_TEMP_TABLE(A) (A->tmp_table != NO_TMP_TABLE)
 
 extern SYMBOL symbols[];
 extern size_t symbols_length;
@@ -151,6 +152,7 @@ static int show_create_sequence(THD *thd, TABLE_LIST *table_list,
 static const LEX_CSTRING *view_algorithm(TABLE_LIST *table);
 
 bool get_lookup_field_values(THD *, COND *, TABLE_LIST *, LOOKUP_FIELD_VALUES *);
+void process_i_s_table_temporary_tables(THD *thd, TABLE * table, TABLE *tmp_tbl);
 
 /**
   Try to lock a mutex, but give up after a short while to not cause deadlocks
@@ -5127,6 +5129,7 @@ public:
   }
 };
 
+
 /**
   @brief          Fill I_S tables whose data are retrieved
                   from frm files and storage engine
@@ -5231,6 +5234,25 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
   init_alloc_root(PSI_INSTRUMENT_ME, &tmp_mem_root, SHOW_ALLOC_BLOCK_SIZE,
                   SHOW_ALLOC_BLOCK_SIZE, MY_THREAD_SPECIFIC);
 
+  /* Handling session temporary tables from the backup state*/
+  if (schema_table_idx == SCH_TABLES)
+  {
+    if (open_tables_state_backup.temporary_tables)
+    {
+      All_tmp_tables_list::Iterator it(*open_tables_state_backup.temporary_tables);
+      TMP_TABLE_SHARE *share_temp;
+      while ((share_temp= it++))
+      {
+        All_share_tables_list::Iterator it2(share_temp->all_tmp_tables);
+        while (TABLE *tmp_tbl= it2++)
+        {
+          if (IS_USER_TEMP_TABLE(share_temp))
+            process_i_s_table_temporary_tables(thd, table, tmp_tbl);
+        }
+      }
+    }
+  }
+
   for (size_t i=0; i < db_names.elements(); i++)
   {
     LEX_CSTRING *db_name= db_names.at(i);
@@ -5251,9 +5273,9 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
       if (unlikely(res))
         goto err;
 
-      for (size_t i=0; i < table_names.elements(); i++)
+      for (size_t j=0; j < table_names.elements(); j++)
       {
-        LEX_CSTRING *table_name= table_names.at(i);
+        LEX_CSTRING *table_name= table_names.at(j);
         DBUG_ASSERT(table_name->length <= NAME_LEN);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -5503,6 +5525,8 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables,
       table->field[3]->store(STRING_WITH_LEN("SYSTEM VIEW"), cs);
     else if (share->table_type == TABLE_TYPE_SEQUENCE)
       table->field[3]->store(STRING_WITH_LEN("SEQUENCE"), cs);
+    else if (IS_USER_TEMP_TABLE(share))
+      table->field[3]->store(STRING_WITH_LEN("TEMPORARY"), cs);
     else
     {
       DBUG_ASSERT(share->tmp_table == NO_TMP_TABLE);
@@ -5780,6 +5804,27 @@ err:
 
 
 /**
+ @brief           Fill IS.table with temporary tables
+ @details         The function does...
+ @param[in]       table                I_S table (TABLE)
+ @param[in]       db_name              db name of temporary table
+ @param[in]       table_name           table name of temporary table
+ @return          Operation status
+   @retval        0   - success
+   @retval        1   - failure
+*/
+void process_i_s_table_temporary_tables(THD *thd, TABLE * table, TABLE *tmp_tbl)
+{
+  TABLE_LIST table_list;
+  bzero((char*) &table_list, sizeof(TABLE_LIST));
+  table_list.table= tmp_tbl;
+
+  get_schema_tables_record(thd, &table_list, table,
+                           0, &tmp_tbl->s->db, &tmp_tbl->s->table_name);
+}
+
+
+/**
   @brief    Store field characteristics into appropriate I_S table columns
 
   @param[in]      table             I_S table
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 398a635c927..73b70cd043b 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4304,7 +4304,9 @@ int create_table_impl(THD *thd,
   handler	*file= 0;
   int		error= 1;
   bool          frm_only= create_table_mode == C_ALTER_TABLE_FRM_ONLY;
-  bool          internal_tmp_table= create_table_mode == C_ALTER_TABLE || frm_only;
+  bool          internal_tmp_table= (create_table_mode == C_ALTER_TABLE ||
+                                     frm_only);
+  bool          warning_given= 0;
   DBUG_ENTER("create_table_impl");
   DBUG_PRINT("enter", ("db: '%s'  table: '%s'  tmp: %d  path: %s",
                        db.str, table_name.str, internal_tmp_table, path.str));
@@ -4314,6 +4316,15 @@ int create_table_impl(THD *thd,
   {
     ddl_log_state_create= 0;
     ddl_log_state_rm= 0;
+
+    if (ha_table_exists(thd, &orig_db, &orig_table_name, NULL, NULL, NULL))
+    {
+      warning_given= 1;
+      push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                          ER_TABLE_EXISTS_ERROR,
+                          ER_THD(thd, ER_TABLE_EXISTS_ERROR),
+                          orig_table_name.str);
+    }
   }
 
   if (fix_constraints_names(thd, &alter_info->check_constraint_list,
@@ -4614,10 +4625,11 @@ err:
 
 warn:
   error= -1;
-  push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
-                      ER_TABLE_EXISTS_ERROR,
-                      ER_THD(thd, ER_TABLE_EXISTS_ERROR),
-                      alias->str);
+  if (!warning_given)
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                        ER_TABLE_EXISTS_ERROR,
+                        ER_THD(thd, ER_TABLE_EXISTS_ERROR),
+                        alias->str);
   goto err;
 }