15 files changed, 421 insertions, 41 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 154a8544ca9..b9c95a04968 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1736,7 +1736,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;
 create temporary table if not exists t1 (a int) select * from t1;
-ERROR HY000: Can't reopen table: 't1'
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 a
 1
diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index 381667745d8..0313666d2a5 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -380,12 +380,7 @@ alter table t1 add column j int;
 unlock tables;
 drop table t1;
 create temporary table t1 (i int);
-#
-# This is just for test coverage purposes, 
-# when this is allowed, remove the --error.
-#
 lock tables t1 write, t1 as a read, t1 as b read;
-ERROR HY000: Can't reopen table: 't1'
 alter table t1 add column j int;
 unlock tables;
 drop table t1;
diff --git a/mysql-test/r/reopen_temp_table.result b/mysql-test/r/reopen_temp_table.result
new file mode 100644
index 00000000000..f98ab96747f
--- /dev/null
+++ b/mysql-test/r/reopen_temp_table.result
@@ -0,0 +1,133 @@
+#
+# MDEV-5535: Cannot reopen temporary table
+#
+DROP DATABASE IF EXISTS temp_db;
+CREATE DATABASE temp_db;
+USE temp_db;
+#
+# Reopen temporary table
+#
+CREATE TEMPORARY TABLE t1(i int)ENGINE=INNODB;
+INSERT INTO t1 VALUES(1), (2);
+SELECT * FROM t1 a, t1 b;
+i	i
+1	1
+2	1
+1	2
+2	2
+DROP TABLE t1;
+#
+# CREATE & Stored routines
+#
+CREATE TEMPORARY TABLE t3 AS SELECT 1 AS a;
+CREATE PROCEDURE p1()
+BEGIN
+DROP TEMPORARY TABLE t3;
+end|
+CREATE FUNCTION f3() RETURNS INT
+BEGIN
+CALL p1();
+RETURN 1;
+END|
+PREPARE STMT FROM "SELECT f3() AS my_Column, a FROM t3";
+EXECUTE STMT;
+ERROR HY000: Can't reopen table: 't3'
+DROP TABLE t3;
+DROP FUNCTION f3;
+DROP PROCEDURE p1;
+CREATE TEMPORARY TABLE t4 (i INT);
+INSERT INTO t4 VALUES(1), (2);
+CREATE FUNCTION f4() RETURNS INT
+BEGIN
+DROP TEMPORARY TABLE t4;
+RETURN 1;
+END|
+SELECT f4() FROM t4;
+ERROR HY000: Can't reopen table: 't4'
+SELECT * FROM t4;
+i
+1
+2
+DROP TABLE t4;
+DROP FUNCTION f4;
+CREATE TEMPORARY TABLE t5 AS SELECT 1 AS a;
+CREATE PROCEDURE p2()
+BEGIN
+DROP TEMPORARY TABLE t5;
+END|
+CREATE FUNCTION f5() RETURNS INT
+BEGIN
+CALL p2();
+RETURN 1;
+END|
+SELECT f5() AS my_column, a FROM t5;
+ERROR HY000: Can't reopen table: 't5'
+DROP TABLE t5;
+DROP FUNCTION f5;
+DROP PROCEDURE p2;
+#
+# CTAS
+#
+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;
+SELECT * FROM t1;
+a	b
+1	1
+2	1
+1	2
+2	2
+DROP TABLE t1;
+SELECT * FROM t1;
+i
+1
+2
+DROP TABLE t1;
+#
+# HANDLER
+#
+CREATE TABLE t1 (a INT, KEY a(a));
+INSERT INTO t1 (a) VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 (a INT, KEY a (a)) SELECT * FROM t1;
+CREATE TEMPORARY TABLE t3 (a INT, KEY a (a)) SELECT * FROM t2;
+HANDLER t3 OPEN;
+SELECT * FROM t1;
+a
+1
+2
+3
+4
+5
+LOCK TABLE t1 READ;
+HANDLER t3 OPEN;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+UNLOCK TABLES;
+HANDLER t3 OPEN;
+ERROR 42000: Not unique table/alias: 't3'
+HANDLER t3 READ NEXT;
+a
+1
+HANDLER t3 OPEN AS t3_1;
+HANDLER t3_1 READ NEXT;
+a
+1
+HANDLER t3_1 READ NEXT;
+a
+2
+HANDLER t3 CLOSE;
+HANDLER t3_1 CLOSE;
+DROP TEMPORARY TABLE t3;
+DROP TABLE t1, t2;
+#
+# INSERT-SELECT
+#
+CREATE TEMPORARY TABLE t4 (a INT) ENGINE=MYISAM;
+INSERT INTO t4 VALUES(1), (2);
+INSERT INTO t4 SELECT * FROM t4;
+SELECT COUNT(*) FROM t4;
+COUNT(*)
+4
+DROP TABLE t4;
+# Cleanup
+DROP DATABASE temp_db;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index de6b87d67cd..ce23fe1693e 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1140,9 +1140,12 @@ insert into t3 values (1), (2), (3);
 return (select count(*) from t3 as a, t3 as b);
 end|
 select f11()|
-ERROR HY000: Can't reopen table: 'a'
+f11()
+9
 select f11() from t1|
-ERROR HY000: Can't reopen table: 'a'
+f11()
+9
+9
 create function f12_1() returns int
 begin
 drop temporary table if exists t3;
@@ -1156,6 +1159,7 @@ drop temporary table t3|
 select f12_1()|
 f12_1()
 3
+drop temporary table t3|
 select f12_1() from t1 limit 1|
 f12_1()
 3
@@ -6933,7 +6937,7 @@ CREATE TEMPORARY TABLE t3 LIKE t1;
 CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
 END|
 CALL p1;
-ERROR HY000: Can't reopen table: 'A'
+f1
 CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
 DROP TABLE t3;
 CALL p1;
diff --git a/mysql-test/suite/handler/aria.result b/mysql-test/suite/handler/aria.result
index 43720eaa4bb..b8ed1fd98c8 100644
--- a/mysql-test/suite/handler/aria.result
+++ b/mysql-test/suite/handler/aria.result
@@ -632,7 +632,18 @@ handler a1 read a next;
 a	b
 3	d
 select a,b from t1;
-ERROR HY000: Can't reopen table: 'a1'
+a	b
+0	a
+1	b
+2	c
+3	d
+4	e
+5	f
+6	g
+7	h
+8	i
+9	j
+9	k
 handler a1 read a prev;
 a	b
 2	c
@@ -745,7 +756,7 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
 handler t2 close;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 handler t3 open;
-ERROR HY000: Can't reopen table: 't3'
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 # After UNLOCK TABLES handlers should be around and
 # we should be able to continue reading through them.
 unlock tables;
@@ -1396,7 +1407,12 @@ handler t2 read a next;
 a	b
 3	NULL
 select * from t2;
-ERROR HY000: Can't reopen table: 't2'
+a	b
+1	NULL
+2	NULL
+3	NULL
+4	NULL
+5	NULL
 handler t2 read a next;
 a	b
 4	NULL
diff --git a/mysql-test/suite/handler/handler.inc b/mysql-test/suite/handler/handler.inc
index b1e881f1bd8..a4ab5f1ed32 100644
--- a/mysql-test/suite/handler/handler.inc
+++ b/mysql-test/suite/handler/handler.inc
@@ -460,9 +460,6 @@ drop table t1;
 #
 # Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
 #
-# Test HANDLER statements in conjunction with temporary tables. While the temporary table
-# is open by a HANDLER, no other statement can access it.
-#
 
 create temporary table t1 (a int, b char(1), key a using btree (a), key b using btree (a,b));
 insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
@@ -472,7 +469,6 @@ handler t1 open as a1;
 handler a1 read a=(1);
 handler a1 read a next;
 handler a1 read a next;
---error ER_CANT_REOPEN_TABLE
 select a,b from t1;
 handler a1 read a prev;
 handler a1 read a prev;
@@ -564,7 +560,7 @@ handler t1 open;
 handler t1 read next;
 --error ER_LOCK_OR_ACTIVE_TRANSACTION
 handler t2 close;
---error ER_CANT_REOPEN_TABLE
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
 handler t3 open;
 --echo # After UNLOCK TABLES handlers should be around and
 --echo # we should be able to continue reading through them.
@@ -1182,7 +1178,6 @@ handler t1 read a next;
 select * from t1;
 --echo # Sic: the position is not lost
 handler t2 read a next;
---error ER_CANT_REOPEN_TABLE
 select * from t2;
 handler t2 read a next;
 drop table t1;
diff --git a/mysql-test/suite/handler/heap.result b/mysql-test/suite/handler/heap.result
index e558911e2e1..70dcefe4ff3 100644
--- a/mysql-test/suite/handler/heap.result
+++ b/mysql-test/suite/handler/heap.result
@@ -632,7 +632,18 @@ handler a1 read a next;
 a	b
 3	d
 select a,b from t1;
-ERROR HY000: Can't reopen table: 'a1'
+a	b
+0	a
+1	b
+2	c
+3	d
+4	e
+5	f
+6	g
+7	h
+8	i
+9	j
+9	k
 handler a1 read a prev;
 a	b
 2	c
@@ -745,7 +756,7 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
 handler t2 close;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 handler t3 open;
-ERROR HY000: Can't reopen table: 't3'
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 # After UNLOCK TABLES handlers should be around and
 # we should be able to continue reading through them.
 unlock tables;
@@ -1396,7 +1407,12 @@ handler t2 read a next;
 a	b
 3	NULL
 select * from t2;
-ERROR HY000: Can't reopen table: 't2'
+a	b
+1	NULL
+2	NULL
+3	NULL
+4	NULL
+5	NULL
 handler t2 read a next;
 a	b
 4	NULL
diff --git a/mysql-test/suite/handler/innodb.result b/mysql-test/suite/handler/innodb.result
index fc1089ee445..f3c0f798902 100644
--- a/mysql-test/suite/handler/innodb.result
+++ b/mysql-test/suite/handler/innodb.result
@@ -633,7 +633,18 @@ handler a1 read a next;
 a	b
 3	d
 select a,b from t1;
-ERROR HY000: Can't reopen table: 'a1'
+a	b
+0	a
+1	b
+2	c
+3	d
+4	e
+5	f
+6	g
+7	h
+8	i
+9	j
+9	k
 handler a1 read a prev;
 a	b
 2	c
@@ -747,7 +758,7 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
 handler t2 close;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 handler t3 open;
-ERROR HY000: Can't reopen table: 't3'
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 # After UNLOCK TABLES handlers should be around and
 # we should be able to continue reading through them.
 unlock tables;
@@ -1400,7 +1411,12 @@ handler t2 read a next;
 a	b
 3	NULL
 select * from t2;
-ERROR HY000: Can't reopen table: 't2'
+a	b
+1	NULL
+2	NULL
+3	NULL
+4	NULL
+5	NULL
 handler t2 read a next;
 a	b
 4	NULL
diff --git a/mysql-test/suite/handler/myisam.result b/mysql-test/suite/handler/myisam.result
index 84cd8bbd5d2..fca75f3b7a6 100644
--- a/mysql-test/suite/handler/myisam.result
+++ b/mysql-test/suite/handler/myisam.result
@@ -632,7 +632,18 @@ handler a1 read a next;
 a	b
 3	d
 select a,b from t1;
-ERROR HY000: Can't reopen table: 'a1'
+a	b
+0	a
+1	b
+2	c
+3	d
+4	e
+5	f
+6	g
+7	h
+8	i
+9	j
+9	k
 handler a1 read a prev;
 a	b
 2	c
@@ -745,7 +756,7 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
 handler t2 close;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 handler t3 open;
-ERROR HY000: Can't reopen table: 't3'
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 # After UNLOCK TABLES handlers should be around and
 # we should be able to continue reading through them.
 unlock tables;
@@ -1396,7 +1407,12 @@ handler t2 read a next;
 a	b
 3	NULL
 select * from t2;
-ERROR HY000: Can't reopen table: 't2'
+a	b
+1	NULL
+2	NULL
+3	NULL
+4	NULL
+5	NULL
 handler t2 read a next;
 a	b
 4	NULL
diff --git a/mysql-test/suite/rpl/r/rpl_reopen_temp_table.result b/mysql-test/suite/rpl/r/rpl_reopen_temp_table.result
new file mode 100644
index 00000000000..2c4ffba622b
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_reopen_temp_table.result
@@ -0,0 +1,30 @@
+include/master-slave.inc
+[connection master]
+connection slave;
+reset master;
+#
+# MDEV-5535: Cannot reopen temporary table
+#
+connection master;
+DROP TABLE IF EXISTS t1, t2, t3;
+CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE TEMPORARY TABLE t2 SELECT A.c1 a, B.c1 b FROM t1 AS A, t1 AS B;
+CREATE TABLE t3 SELECT * FROM t2;
+SELECT COUNT(*) = 5 FROM t1;
+COUNT(*) = 5
+1
+SELECT COUNT(*) = 25 FROM t2;
+COUNT(*) = 25
+1
+SELECT COUNT(*) = 25 FROM t3;
+COUNT(*) = 25
+1
+connection slave;
+SELECT COUNT(*) = 25 FROM t3;
+COUNT(*) = 25
+1
+connection master;
+DROP TABLE t1, t2, t3;
+connection slave;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_reopen_temp_table.test b/mysql-test/suite/rpl/t/rpl_reopen_temp_table.test
new file mode 100644
index 00000000000..2396dc348fe
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_reopen_temp_table.test
@@ -0,0 +1,43 @@
+--source include/have_innodb.inc
+--source include/master-slave.inc
+
+# Clean up old slave's binlogs (see rpl_temporary.test for explanation).
+save_master_pos;
+connection slave;
+sync_with_master;
+reset master;
+
+--echo #
+--echo # MDEV-5535: Cannot reopen temporary table
+--echo #
+
+connection master;
+--disable_query_log
+CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+--enable_query_log
+
+disable_warnings;
+DROP TABLE IF EXISTS t1, t2, t3;
+enable_warnings;
+
+CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE TEMPORARY TABLE t2 SELECT A.c1 a, B.c1 b FROM t1 AS A, t1 AS B;
+
+CREATE TABLE t3 SELECT * FROM t2;
+
+SELECT COUNT(*) = 5 FROM t1;
+SELECT COUNT(*) = 25 FROM t2;
+SELECT COUNT(*) = 25 FROM t3;
+
+sync_slave_with_master;
+
+SELECT COUNT(*) = 25 FROM t3;
+
+connection master;
+DROP TABLE t1, t2, t3;
+
+sync_slave_with_master;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 10ee41ca2aa..0fd3e31a5b4 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1618,7 +1618,6 @@ drop view t1;
 
 create table t1 (a int) select 1 as a;
 create temporary table if not exists t1 (a int) select * from t1;
---error ER_CANT_REOPEN_TABLE
 create temporary table if not exists t1 (a int) select * from t1;
 select * from t1;
 drop temporary table t1;
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index 23eb04d7826..2e164de9b93 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -442,11 +442,6 @@ alter table t1 add column j int;
 unlock tables;
 drop table t1;
 create temporary table t1 (i int);
---echo #
---echo # This is just for test coverage purposes, 
---echo # when this is allowed, remove the --error.
---echo #
---error ER_CANT_REOPEN_TABLE
 lock tables t1 write, t1 as a read, t1 as b read;
 alter table t1 add column j int;
 unlock tables;
diff --git a/mysql-test/t/reopen_temp_table.test b/mysql-test/t/reopen_temp_table.test
new file mode 100644
index 00000000000..0cf3f774985
--- /dev/null
+++ b/mysql-test/t/reopen_temp_table.test
@@ -0,0 +1,128 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # MDEV-5535: Cannot reopen temporary table
+--echo #
+
+--disable_warnings
+DROP DATABASE IF EXISTS temp_db;
+--enable_warnings
+
+CREATE DATABASE temp_db;
+USE temp_db;
+
+--echo #
+--echo # Reopen temporary table
+--echo #
+
+CREATE TEMPORARY TABLE t1(i int)ENGINE=INNODB;
+INSERT INTO t1 VALUES(1), (2);
+SELECT * FROM t1 a, t1 b;
+DROP TABLE t1;
+
+--echo #
+--echo # CREATE & Stored routines
+--echo #
+
+CREATE TEMPORARY TABLE t3 AS SELECT 1 AS a;
+DELIMITER |;
+CREATE PROCEDURE p1()
+BEGIN
+  DROP TEMPORARY TABLE t3;
+end|
+CREATE FUNCTION f3() RETURNS INT
+BEGIN
+  CALL p1();
+  RETURN 1;
+END|
+DELIMITER ;|
+PREPARE STMT FROM "SELECT f3() AS my_Column, a FROM t3";
+--error ER_CANT_REOPEN_TABLE
+EXECUTE STMT;
+DROP TABLE t3;
+DROP FUNCTION f3;
+DROP PROCEDURE p1;
+
+CREATE TEMPORARY TABLE t4 (i INT);
+INSERT INTO t4 VALUES(1), (2);
+DELIMITER |;
+CREATE FUNCTION f4() RETURNS INT
+BEGIN
+  DROP TEMPORARY TABLE t4;
+  RETURN 1;
+END|
+DELIMITER ;|
+--error ER_CANT_REOPEN_TABLE
+SELECT f4() FROM t4;
+SELECT * FROM t4;
+DROP TABLE t4;
+DROP FUNCTION f4;
+
+CREATE TEMPORARY TABLE t5 AS SELECT 1 AS a;
+DELIMITER |;
+CREATE PROCEDURE p2()
+BEGIN
+  DROP TEMPORARY TABLE t5;
+END|
+CREATE FUNCTION f5() RETURNS INT
+BEGIN
+  CALL p2();
+  RETURN 1;
+END|
+DELIMITER ;|
+--error ER_CANT_REOPEN_TABLE
+SELECT f5() AS my_column, a FROM t5;
+DROP TABLE t5;
+DROP FUNCTION f5;
+DROP PROCEDURE p2;
+
+--echo #
+--echo # CTAS
+--echo #
+
+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;
+SELECT * FROM t1;
+DROP TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # HANDLER
+--echo #
+
+CREATE TABLE t1 (a INT, KEY a(a));
+INSERT INTO t1 (a) VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 (a INT, KEY a (a)) SELECT * FROM t1;
+CREATE TEMPORARY TABLE t3 (a INT, KEY a (a)) SELECT * FROM t2;
+HANDLER t3 OPEN;
+SELECT * FROM t1;
+LOCK TABLE t1 READ;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+HANDLER t3 OPEN;
+UNLOCK TABLES;
+--error ER_NONUNIQ_TABLE
+HANDLER t3 OPEN;
+HANDLER t3 READ NEXT;
+HANDLER t3 OPEN AS t3_1;
+HANDLER t3_1 READ NEXT;
+HANDLER t3_1 READ NEXT;
+HANDLER t3 CLOSE;
+HANDLER t3_1 CLOSE;
+DROP TEMPORARY TABLE t3;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # INSERT-SELECT
+--echo #
+
+CREATE TEMPORARY TABLE t4 (a INT) ENGINE=MYISAM;
+INSERT INTO t4 VALUES(1), (2);
+INSERT INTO t4 SELECT * FROM t4;
+SELECT COUNT(*) FROM t4;
+DROP TABLE t4;
+
+--echo # Cleanup
+DROP DATABASE temp_db;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 9e6b919ea19..c80e1eaaa3e 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -1368,11 +1368,6 @@ select f10()|
 create table t4 as select 1 as id|
 select f10()|
 
-# Practical cases which we don't handle well (yet)
-#
-# Function which does not work because of well-known and documented
-# limitation of MySQL. We can't use the several instances of the
-# same temporary table in statement.
 create function f11() returns int
 begin
   drop temporary table if exists t3;
@@ -1380,9 +1375,7 @@ begin
   insert into t3 values (1), (2), (3);
   return (select count(*) from t3 as a, t3 as b);
 end|
---error ER_CANT_REOPEN_TABLE
 select f11()|
---error ER_CANT_REOPEN_TABLE
 select f11() from t1|
 # Test that using a single table instance at a time works
 create function f12_1() returns int
@@ -1397,6 +1390,7 @@ create function f12_2() returns int
 
 drop temporary table t3|
 select f12_1()|
+drop temporary table t3|
 select f12_1() from t1 limit 1|
 
 # Cleanup
@@ -8278,7 +8272,6 @@ delimiter |;
 CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
 END|
 delimiter ;|
---error ER_CANT_REOPEN_TABLE
 CALL p1;
 CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
 DROP TABLE t3;