diff options
Diffstat (limited to 'mysql-test/main/temp_table.test')
| -rw-r--r-- | mysql-test/main/temp_table.test | 597 |
1 files changed, 597 insertions, 0 deletions
diff --git a/mysql-test/main/temp_table.test b/mysql-test/main/temp_table.test new file mode 100644 index 00000000000..fb44362b537 --- /dev/null +++ b/mysql-test/main/temp_table.test @@ -0,0 +1,597 @@ +# mysqltest should be fixed +--source include/not_embedded.inc +--source include/have_innodb.inc + +# +# Test of temporary tables +# + +--disable_warnings +drop table if exists t1,t2; +drop view if exists v1; +--enable_warnings + +--echo # +--echo # test basic creation of temporary tables together with normal table +--echo # + +create table t1 (a int); +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 (a int); +drop temporary table t1; +drop table t1; + +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 (a int); +drop temporary table t1; + +--echo # +--echo # Test with rename +--echo # + +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; +select * from t2; +CREATE TABLE t2 (x int not null, y int not null); +alter table t2 rename t1; +select * from t1; +create TEMPORARY TABLE t2 engine=heap select * from t1; +create TEMPORARY TABLE IF NOT EXISTS t2 (a int) engine=heap; + +# This should give errors +--error 1050 +CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null); +--error 1050 +ALTER TABLE t1 RENAME t2; + +select * from t2; +alter table t2 add primary key (a,b); +drop table t1,t2; +select * from t1; +drop table t2; +create temporary table t1 select *,2 as "e" from t1; +select * from t1; +drop table t1; +drop table t1; + +# +# Test CONCAT_WS with temporary tables +# + +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; +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; + +# +# Problem with ELT +# +create temporary table t1 (id int(10) not null unique); +create temporary table t2 (id int(10) not null primary key, +val int(10) not null); + +# put in some initial values +insert into t1 values (1),(2),(4); +insert into t2 values (1,1),(2,1),(3,1),(4,2); + +# do a query using ELT, a join and an ORDER BY. +select one.id, two.val, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id; +drop table t1,t2; + +# +# Test of failed ALTER TABLE on temporary table +# +create temporary table t1 (a int not null); +insert into t1 values (1),(1); +-- error ER_DUP_ENTRY +alter table t1 add primary key (a); +drop table t1; + +# +# In MySQL 4.0.4 doing a GROUP BY on a NULL column created a disk based +# temporary table when a memory based one would be good enough. + +CREATE TABLE t1 ( + d datetime default NULL +) ENGINE=MyISAM; + + +INSERT INTO t1 VALUES ('2002-10-24 14:50:32'),('2002-10-24 14:50:33'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40'); + +flush status; +select * from t1 group by d; +show status like "created_tmp%tables"; +drop table t1; + +# Fix for BUG#8921: Check that temporary table is ingored by view commands. +create temporary table v1 as select 'This is temp. table' A; +create view v1 as select 'This is view' A; +select * from v1; +show create table v1; +show create view v1; +drop view v1; +select * from v1; +create view v1 as select 'This is view again' A; +select * from v1; +drop table v1; +select * from v1; +drop view v1; + +# Bug #8497: tmpdir with extra slashes would cause failures +# +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; +drop table t1, t2; + + +# +# BUG#21096: locking issue ; temporary table conflicts. +# +# The problem was that on DROP TEMPORARY table name lock was acquired, +# which should not be done. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT); + +LOCK TABLE t1 WRITE; + +connect (conn1, localhost, root,,); + +CREATE TEMPORARY TABLE t1 (i INT); + +--echo The following command should not block +DROP TEMPORARY TABLE t1; + +disconnect conn1; +connection default; + +DROP TABLE t1; + +# +# Check that it's not possible to drop a base table with +# DROP TEMPORARY statement. +# +CREATE TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); + +--error 1051 +DROP TEMPORARY TABLE t2, t1; + +# Table t2 should have been dropped. +--error 1146 +SELECT * FROM t2; +# But table t1 should still be there. +SELECT * FROM t1; + +DROP TABLE t1; + + +--echo End of 4.1 tests. + +# +# Bug #24791: Union with AVG-groups generates wrong results +# +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); + +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); + +SELECT AVG( c ) FROM t1 UNION SELECT 1; +SELECT 1 UNION SELECT AVG( c ) FROM t1; +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +SELECT c/1 FROM t1 UNION SELECT 1; + +DROP TABLE t1, t2; + +# +# Test truncate with temporary tables +# + +create temporary table t1 (a int); +insert into t1 values (4711); +select * from t1; +truncate t1; +insert into t1 values (42); +select * from t1; +drop table t1; + +# +# Bug #35392: Delete all statement does not execute properly after +# few delete statements +# +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; +DELETE FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table +# + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +CREATE TEMPORARY TABLE t1 (a INT); +CREATE TEMPORARY TABLE t2 LIKE t1; + +DELIMITER |; +CREATE FUNCTION f1() RETURNS INT + BEGIN + return 1; + END| +DELIMITER ;| + +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; + +--echo # +--echo # Bug #48067: A temp table with the same name as an existing table, +--echo # makes drop database fail. +--echo # +--disable_warnings +DROP TEMPORARY TABLE IF EXISTS bug48067.t1; +DROP DATABASE IF EXISTS bug48067; +--enable_warnings +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; + +--echo End of 5.1 tests + +--echo # +--echo # Test that admin statements work for temporary tables. +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings +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; + +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; + +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (11), (12), (13); +INSERT INTO t3 VALUES (101), (102), (103); + +--replace_column 2 xxx +CHECKSUM TABLE t1, t2, t3; + +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; + +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; + +DROP TABLES t1, t2, t3; + +CREATE TEMPORARY TABLE t1 (a int); +RENAME TABLE t1 TO t2; +DROP TABLE t2; + +# +# CREATE TEMPORARY TEMPORARY TABLE +# +--error ER_PARSE_ERROR +create temporary temporary table t1 (a int); + +# +# MDEV-7832 Add status variables to track CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE +# +flush status; +create table t1 (a int); +create temporary table t2 (a int); +create temporary table t3 (a int); +drop table t1; +drop table t2; +drop temporary table t3; +show status like 'com_create%table'; +show status like 'com_drop%table'; + +--echo # +--echo # Some more generic temporary table tests +--echo # added during MDEV-5535. +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS temp_db; +--enable_warnings + +CREATE DATABASE temp_db; +USE temp_db; + +--echo # +--echo # SHOW TABLES do not list temporary tables. +--echo # + +CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB; +INSERT INTO temp_t1 VALUES(1); +SELECT * FROM temp_t1; +SHOW TABLES; +DROP TABLE temp_t1; + +--echo # +--echo # Create and drop a temporary table. +--echo # + +CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB; +INSERT INTO temp_t1 VALUES(1); +SELECT * FROM temp_t1; +DROP TABLE temp_t1; +--error ER_NO_SUCH_TABLE +SELECT * FROM temp_t1; + +--echo # +--echo # Create a temporary table and base table with same name and DROP TABLE. +--echo # + +CREATE TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB; +INSERT INTO t1 VALUES("BASE TABLE"); +# Temporary table shadows the base table with the same name. +CREATE TEMPORARY TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB; +INSERT INTO t1 VALUES("TEMPORARY TABLE"); +SELECT * FROM t1; +# Only temporary table should get dropped. +DROP TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +--error ER_NO_SUCH_TABLE +SELECT * FROM t1; + +--echo # +--echo # Create a temporary table and base table with same name and DROP TEMPORARY +--echo # TABLE. +--echo # + +CREATE TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB; +INSERT INTO t1 VALUES("BASE TABLE"); +# Temporary table shadows the base table with the same name. +CREATE TEMPORARY TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB; +INSERT INTO t1 VALUES("TEMPORARY TABLE"); +SELECT * FROM t1; +# Only temporary table should get dropped. +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; +--error ER_BAD_TABLE_ERROR +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Create a temporary table and drop its parent database. +--echo # + +USE temp_db; +CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB; +INSERT INTO temp_t1 VALUES (1); +# Drop database +DROP DATABASE temp_db; +CREATE DATABASE temp_db; +USE temp_db; +# Temporary tables are not physically tied to schemas +DROP TEMPORARY TABLE temp_t1; + +--echo # +--echo # Similar to above, but this time with a base table with same name. +--echo # + +USE temp_db; +CREATE TABLE t1(i INT)ENGINE=INNODB; +CREATE TEMPORARY TABLE t1(i INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (1); +# Drop database +DROP DATABASE temp_db; +CREATE DATABASE temp_db; +USE temp_db; +# Temporary tables are not physically tied to schemas +DROP TEMPORARY TABLE t1; +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; + +--echo # +--echo # Create a temporary table within a function. +--echo # + +USE temp_db; +delimiter |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + DROP TEMPORARY TABLE IF EXISTS temp_t1; + CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB; + INSERT INTO `temp_t1` VALUES(1); + RETURN (SELECT COUNT(*) FROM temp_t1); +END| +delimiter ;| + +SELECT f1(); +SELECT * FROM temp_t1; + +DROP TABLE temp_t1; +CREATE TEMPORARY TABLE `temp_t1`(i INT) ENGINE=INNODB; +SELECT f1(); +SELECT * FROM temp_t1; +DROP FUNCTION f1; + +--echo # +--echo # Create and drop a temporary table within a function. +--echo # + +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION f2() RETURNS INT +BEGIN + DROP TEMPORARY TABLE IF EXISTS temp_t1; + CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB; + INSERT INTO temp_t1 VALUES(1); + DROP TABLE temp_t1; + RETURN 0; +END| + +CREATE FUNCTION f2() RETURNS INT +BEGIN + DROP TEMPORARY TABLE IF EXISTS temp_t1; + CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB; + INSERT INTO temp_t1 VALUES(1); + DROP TEMPORARY TABLE temp_t1; + RETURN 0; +END| +delimiter ;| +SELECT f2(); +DROP FUNCTION f2; + +--echo # +--echo # Create a temporary table within a function and select it from another +--echo # function. +--echo # + +delimiter |; +CREATE FUNCTION f2() RETURNS INT +BEGIN + DROP TEMPORARY TABLE IF EXISTS temp_t1; + CREATE TEMPORARY TABLE temp_t1 (i INT) ENGINE=INNODB; + INSERT INTO temp_t1 VALUES (1); + RETURN f2_1(); +END| + +CREATE FUNCTION f2_1() RETURNS INT + RETURN (SELECT COUNT(*) FROM temp_t1)| +delimiter ;| + +SELECT f2(); +DROP TEMPORARY TABLE temp_t1; +DROP FUNCTION f2; + +--echo # +--echo # Create temporary table like base table. +--echo # + +CREATE TABLE t1(i INT) ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +CREATE TEMPORARY TABLE temp_t1 LIKE t1; +SELECT * FROM temp_t1; +--error ER_NONUNIQ_TABLE +CREATE TEMPORARY TABLE t1 LIKE t1; +DROP TABLE temp_t1, t1; + +--echo # +--echo # Create temporary table as base table. +--echo # + +CREATE TABLE t1(i INT) ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +CREATE TEMPORARY TABLE temp_t1 AS SELECT * FROM t1; +SELECT * FROM temp_t1; +DROP TABLE temp_t1, t1; + +--echo # +--echo # ALTER TABLE RENAME & ENABLE/DISABLE KEYS (shortcuts) +--echo # +CREATE TEMPORARY TABLE t1(i INT PRIMARY KEY) ENGINE=MYISAM; +INSERT INTO t1 VALUES(1); +SELECT COUNT(*)=1 FROM t1; + +ALTER TABLE t1 RENAME t2; +SELECT COUNT(*)=1 FROM t2; +ALTER TABLE t2 RENAME t1; + +ALTER TABLE t1 DISABLE KEYS; +ALTER TABLE t1 ENABLE KEYS; + +# LOCK TABLES is ignored for temporary tables. +LOCK TABLES t1 WRITE; +ALTER TABLE t1 RENAME t2; +SELECT COUNT(*)=1 FROM t2; +ALTER TABLE t2 RENAME t1; +ALTER TABLE t1 DISABLE KEYS; +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; + +LOCK TABLES t1 READ; +ALTER TABLE t1 RENAME t2; +SELECT COUNT(*)=1 FROM t2; +ALTER TABLE t2 RENAME t1; +ALTER TABLE t1 DISABLE KEYS; +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; + +FLUSH TABLES WITH READ LOCK; +ALTER TABLE t1 RENAME t2; +SELECT COUNT(*)=1 FROM t2; +ALTER TABLE t2 RENAME t1; +ALTER TABLE t1 DISABLE KEYS; +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; + +ALTER TABLE t1 RENAME t2, LOCK SHARED; +ALTER TABLE t2 RENAME t1, LOCK EXCLUSIVE; + +DROP TABLE t1; + +--echo # +--echo # MDEV-10792: Assertion `thd->mdl_context.is_lock_owner +--echo # (MDL_key::TABLE, table->db, table->table_name, MDL_SHARED)' +--echo # failed in mysql_rm_table_no_locks +--echo # +CREATE TEMPORARY TABLE t1 (i INT); +--error ER_BAD_TABLE_ERROR +DROP TABLE nonexisting_table, t1; + +--echo # Cleanup +DROP DATABASE temp_db; + |