drop table if exists t1,t2;
drop view if exists v1;
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);
insert into t1 values(4,"e"),(5,"f"),(6,"g");
alter table t1 rename t2;
select * from t1;
c	d
1	
2	a
3	b
select * from t2;
a	b
4	e
5	f
6	g
CREATE TABLE t2 (x int not null, y int not null);
alter table t2 rename t1;
select * from t1;
a	b
4	e
5	f
6	g
create TEMPORARY TABLE t2 engine=heap select * from t1;
create TEMPORARY TABLE IF NOT EXISTS t2 (a int) engine=heap;
Warnings:
Note	1050	Table 't2' already exists
CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null);
ERROR 42S01: Table 't1' already exists
ALTER TABLE t1 RENAME t2;
ERROR 42S01: Table 't2' already exists
select * from t2;
a	b
4	e
5	f
6	g
alter table t2 add primary key (a,b);
drop table t1,t2;
select * from t1;
c	d
1	
2	a
3	b
drop table t2;
create temporary table t1 select *,2 as "e" from t1;
select * from t1;
c	d	e
1		2
2	a	2
3	b	2
drop table t1;
drop table t1;
CREATE TABLE t1 (pkCrash INTEGER PRIMARY KEY,strCrash VARCHAR(255));
INSERT INTO t1 ( pkCrash, strCrash ) VALUES ( 1, '1');
SELECT CONCAT_WS(pkCrash, strCrash) FROM t1;
CONCAT_WS(pkCrash, strCrash)
1
drop table t1;
create temporary table t1 select 1 as 'x';
drop table t1;
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TEMPORARY TABLE tmp SELECT *, NULL FROM t1;
drop table t1;
create temporary table t1 (id int(10) not null unique);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
create temporary table t2 (id int(10) not null primary key,
val int(10) not null);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 values (1),(2),(4);
insert into t2 values (1,1),(2,1),(3,1),(4,2);
select one.id, two.val, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id;
id	val	elt(two.val,'one','two')
1	1	one
2	1	one
4	2	two
drop table t1,t2;
create temporary table t1 (a int not null);
insert into t1 values (1),(1);
alter table t1 add primary key (a);
ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
drop table t1;
create temporary table v1 as select 'This is temp. table' A;
create view v1 as select 'This is view' A;
select * from v1;
A
This is temp. table
show create table v1;
Table	Create Table
v1	CREATE TEMPORARY TABLE `v1` (
  `A` varchar(19) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'This is view' AS `A`	utf8mb4	utf8mb4_0900_ai_ci
drop view v1;
select * from v1;
A
This is temp. table
create view v1 as select 'This is view again' A;
select * from v1;
A
This is temp. table
drop table v1;
select * from v1;
A
This is view again
drop view v1;
create table t1 (a int, b int, index(a), index(b));
create table t2 (c int auto_increment, d varchar(255), primary key (c));
insert into t1 values (3,1),(3,2);
insert into t2 values (NULL, 'foo'), (NULL, 'bar');
select d, c from t1 left join t2 on b = c where a = 3 order by d;
d	c
bar	2
foo	1
drop table t1, t2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT);
LOCK TABLE t1 WRITE;
CREATE TEMPORARY TABLE t1 (i INT);
The following command should not block
DROP TEMPORARY TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (i INT);
CREATE TEMPORARY TABLE t2 (i INT);
DROP TEMPORARY TABLE t2, t1;
ERROR 42S02: Unknown table 'test.t1'
SELECT * FROM t2;
i
SELECT * FROM t1;
i
DROP TABLE t1;
End of 4.1 tests.
CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
Warnings:
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES( 12139 );
CREATE TABLE t2 ( c FLOAT(30,18) );
Warnings:
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES( 123456 );
SELECT AVG( c ) FROM t1 UNION SELECT 1;
AVG( c )
12139.000000000000000000
1.000000000000000000
SELECT 1 UNION SELECT AVG( c ) FROM t1;
1
1.000000000000000000
12139.000000000000000000
SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
1
1
123456
SELECT c/1 FROM t1 UNION SELECT 1;
c/1
12139.000000000000000000
1.000000000000000000
DROP TABLE t1, t2;
create temporary table t1 (a int);
insert into t1 values (4711);
select * from t1;
a
4711
truncate t1;
insert into t1 values (42);
select * from t1;
a
42
drop table t1;
CREATE TEMPORARY TABLE t1(a INT, b VARCHAR(20));
INSERT INTO t1 VALUES(1, 'val1'), (2, 'val2'), (3, 'val3');
DELETE FROM t1 WHERE a=1;
SELECT count(*) FROM t1;
count(*)
2
DELETE FROM t1;
SELECT * FROM t1;
a	b
DROP TABLE t1;
DROP TABLE IF EXISTS t1,t2;
DROP FUNCTION IF EXISTS f1;
CREATE TEMPORARY TABLE t1 (a INT);
CREATE TEMPORARY TABLE t2 LIKE t1;
CREATE FUNCTION f1() RETURNS INT
BEGIN
return 1;
END|
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t1 SELECT f1();
CREATE TABLE t3 SELECT * FROM t1;
INSERT INTO t1 SELECT f1();
UPDATE t1,t2 SET t1.a = t2.a;
INSERT INTO t2 SELECT f1();
DROP TABLE t1,t2,t3;
DROP FUNCTION f1;
#
# Bug #48067: A temp table with the same name as an existing table,
#             makes drop database fail.
#
DROP TEMPORARY TABLE IF EXISTS bug48067.t1;
DROP DATABASE IF EXISTS bug48067;
CREATE DATABASE bug48067;
CREATE TABLE bug48067.t1 (c1 int);
INSERT INTO bug48067.t1 values (1);
CREATE TEMPORARY TABLE bug48067.t1 (c1 int);
DROP DATABASE bug48067;
DROP TEMPORARY table bug48067.t1;
End of 5.1 tests
#
# Test that admin statements work for temporary tables.
#
DROP TABLE IF EXISTS t1,t2;
CREATE TEMPORARY TABLE t1(a INT);
CREATE TEMPORARY TABLE t2(b INT);
CREATE TEMPORARY TABLE t3(c INT);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (11), (12), (13);
INSERT INTO t3 VALUES (101), (102), (103);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (11), (12), (13);
INSERT INTO t3 VALUES (101), (102), (103);
CHECK TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
test.t3	check	status	OK
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (11), (12), (13);
INSERT INTO t3 VALUES (101), (102), (103);
CHECKSUM TABLE t1, t2, t3;
Table	Checksum
test.t1	xxx
test.t2	xxx
test.t3	xxx
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (11), (12), (13);
INSERT INTO t3 VALUES (101), (102), (103);
OPTIMIZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t1	optimize	status	OK
test.t2	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t2	optimize	status	OK
test.t3	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t3	optimize	status	OK
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (11), (12), (13);
INSERT INTO t3 VALUES (101), (102), (103);
REPAIR TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	repair	note	The storage engine for the table doesn't support repair
test.t2	repair	note	The storage engine for the table doesn't support repair
test.t3	repair	note	The storage engine for the table doesn't support repair
DROP TABLES t1, t2, t3;
#
# Bug#19497209 SEGMENTATION FAULT IN FILL_RECORD FOR MULTI_UPDATE
# Bug#19628808 MULTI-UPDATE CRASH WITH LONG PRIMARY KEY
#
CREATE TABLE t1 (
a VARCHAR(255) NOT NULL,
b VARCHAR(255) NOT NULL,
c1 VARCHAR(255) DEFAULT NULL,
d VARCHAR(255) NOT NULL,
PRIMARY KEY (a,b,d)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 (a,b,d) VALUES(1,4,3),(2,5,4);
UPDATE t1 AS t2 STRAIGHT_JOIN t1 SET t1.c1=t1.c1+1;
DROP TABLE t1;
#
# Bug#20145024: WRONG RESULT FOR COUNT DISTINCT QUERY IN DERIVED TABLE
#
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(1,2),(3,4);
CREATE TABLE t2(a INT, b INT);
INSERT INTO t2 VALUES(1,2);
INSERT INTO t2 VALUES(4,5);
SELECT * FROM (SELECT COUNT(DISTINCT t1.a), t1.b FROM t1 RIGHT JOIN t2 ON
t1.a=t2.a GROUP BY t1.b) as tt;
COUNT(DISTINCT t1.a)	b
0	NULL
1	2
DROP TABLE t1,t2;
#
# Bug24713918: ASSERTION `MDL_CHECKER::IS_READ_LOCKED(M_THD, *OBJECT)'
#              FAILED.
#
CREATE DATABASE db1;
CREATE TABLE t1(a INT);
CREATE TEMPORARY TABLE t1(c1 INT);
ALTER TABLE t1 RENAME TO db1.t1;
DROP DATABASE db1;
DROP TABLE t1;
#
# Bug#25841895: ALTERING A TEMPORARY TABLE IN A NON-EXISTENT
#               SCHEMA RAISES ASSERT
#
# Creating a temporary table in a non-existent schema is now disallowed
CREATE TEMPORARY TABLE bogus.t1(a INT);
ERROR 42000: Unknown database 'bogus'
CREATE SCHEMA bogus;
CREATE TEMPORARY TABLE bogus.t1(a INT);
CREATE TEMPORARY TABLE bogus.t2(a INT);
DROP SCHEMA bogus;
# Dropping a schema does not delete temporary tables, so they can
# still be selected from.
SELECT * FROM bogus.t1;
a
# ... but not altered.
ALTER TABLE bogus.t1 ADD COLUMN (b INT);
ERROR 42000: Unknown database 'bogus'
# ... but they can dropped using DROP TEMPORARY TABLE.
DROP TEMPORARY TABLE bogus.t1;
# ... or ordinary DROP TABLE.
DROP TABLE bogus.t2;
SELECT * FROM bogus.t1;
ERROR 42000: Unknown database 'bogus'
SELECT * FROM bogus.t2;
ERROR 42000: Unknown database 'bogus'
#
# Bug#25951134: MEMORY LEAK OF INTERNAL TEMPORARY TABLES
#
CREATE TABLE t1(a LONGTEXT);
SELECT (EXISTS (SELECT 1 FROM t1
GROUP BY (NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM t1 GROUP BY a)r))))
FROM t1;
(EXISTS (SELECT 1 FROM t1
GROUP BY (NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM t1 GROUP BY a)r))))
DROP TABLE t1;
# End of test for bug#25951134
#
# Bug#26126789: lock0lock.cc: ((m_mode - (0xful & m_mode)) - (0xf0ul & m_mode) - (256 & m_mode))
#
# Failed create of temporary table that would leave REPLACE flag in Innodb txn after
# the statement had finished
CREATE TEMPORARY TABLE t1 (a TEXT NOT NULL) REPLACE AS SELECT total_allocated FROM sys.memory_by_thread_by_current_bytes;
ERROR HY000: Field 'a' doesn't have a default value
# Create table would assert because REPLACE setting is not compatible with MDL for CREATE
CREATE TABLE IF NOT EXISTS t2 (a INT);
DROP TABLE t2;
#
# Bug #30716037 MEMORY CORRUPTION BUG IN MYSQLD
#
CREATE TEMPORARY TABLE v0 ( v1 INT UNIQUE ) ;
WITH RECURSIVE v0 ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM v0 )
SELECT 'x' , -1 , 54703121.000000 FROM v0 AS v3 , v0 AS v2 , v0 NATURAL JOIN
v0 AS v5 NATURAL JOIN v0 AS v4 ;
ERROR HY000: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
DROP TABLE v0;
WITH RECURSIVE v0 ( c1 ) AS (
SELECT 0
UNION
SELECT c1 + 1 FROM v0
)
SELECT *
FROM
v0,
v0 AS v1
NATURAL JOIN v0 AS v2;
ERROR HY000: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
WITH RECURSIVE v0 ( c1 ) AS (
SELECT 0
UNION
SELECT 1 FROM v0
)
SELECT *
FROM
v0,
v0 AS v1
NATURAL JOIN v0 AS v2;
c1	c1
0	0
0	1
1	0
1	1
#
# Bug#29899151: CORE AT CREATE TABLE WITH FK FOR TABLE TRANSFERRED
# FROM MYISAM TO INNODB
#
# Verify that default_tmp_storage_engine is substituted and works
# when the source storage engine of LIKE does not support
# temporary tables.
CREATE TEMPORARY TABLE tt1(c1 INT);
SHOW CREATE TABLE tt1;
Table	Create Table
tt1	CREATE TEMPORARY TABLE `tt1` (
  `c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE tt1;
CREATE TEMPORARY TABLE tt1 LIKE performance_schema.setup_consumers;
SHOW CREATE TABLE tt1;
Table	Create Table
tt1	CREATE TEMPORARY TABLE `tt1` (
  `NAME` varchar(64) NOT NULL,
  `ENABLED` enum('YES','NO') NOT NULL,
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE tt1;
SET default_tmp_storage_engine=MYISAM;
CREATE TEMPORARY TABLE tt1(c1 INT);
SHOW CREATE TABLE tt1;
Table	Create Table
tt1	CREATE TEMPORARY TABLE `tt1` (
  `c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE tt1;
CREATE TEMPORARY TABLE tt1 LIKE performance_schema.setup_consumers;
SHOW CREATE TABLE tt1;
Table	Create Table
tt1	CREATE TEMPORARY TABLE `tt1` (
  `NAME` varchar(64) NOT NULL,
  `ENABLED` enum('YES','NO') NOT NULL,
  PRIMARY KEY (`NAME`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE tt1;