diff options
Diffstat (limited to 'mysql-test/main/truncate.test')
-rw-r--r-- | mysql-test/main/truncate.test | 168 |
1 files changed, 168 insertions, 0 deletions
diff --git a/mysql-test/main/truncate.test b/mysql-test/main/truncate.test new file mode 100644 index 00000000..592c234a --- /dev/null +++ b/mysql-test/main/truncate.test @@ -0,0 +1,168 @@ +# +# Test of truncate +# + +create table t1 (a integer, b integer,c1 CHAR(10)); +insert into t1 (a) values (1),(2); +truncate table t1; +select count(*) from t1; +insert into t1 values(1,2,"test"); +select count(*) from t1; +delete from t1; +select * from t1; +drop table t1; +# The following should fail +--error 1146 +select count(*) from t1; +create temporary table t1 (n int); +insert into t1 values (1),(2),(3); +truncate table t1; +select * from t1; +drop table t1; +--error 1146 +truncate non_existing_table; + +# +# test autoincrement with TRUNCATE; verifying difference with DELETE +# + +create table t1 (a integer auto_increment primary key); +insert into t1 (a) values (NULL),(NULL); +truncate table t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +delete from t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +drop table t1; + +# Verifying that temp tables are handled the same way + +create temporary table t1 (a integer auto_increment primary key); +insert into t1 (a) values (NULL),(NULL); +truncate table t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +delete from t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +drop table t1; + +# End of 4.1 tests + +# Test for Bug#5507 "TRUNCATE should work with views" +# +# when it'll be fixed, the error should become 1347 +# (test.v1' is not of type 'BASE TABLE') +# + +create table t1 (s1 int); +insert into t1 (s1) values (1), (2), (3), (4), (5); +create view v1 as select * from t1; +--error 1146 +truncate table v1; +drop view v1; +drop table t1; + +# End of 5.0 tests + +--echo # +--echo # Bug#20667 - Truncate table fails for a write locked table +--echo # + +--disable_service_connection + +CREATE TABLE t1 (c1 INT); +LOCK TABLE t1 WRITE; +INSERT INTO t1 VALUES (1); +SELECT * FROM t1; +TRUNCATE TABLE t1; +SELECT * FROM t1; +UNLOCK TABLES; +# +LOCK TABLE t1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +TRUNCATE TABLE t1; +UNLOCK TABLES; +# +CREATE TABLE t2 (c1 INT); +LOCK TABLE t2 WRITE; +--error ER_TABLE_NOT_LOCKED +TRUNCATE TABLE t1; +UNLOCK TABLES; +# +CREATE VIEW v1 AS SELECT t1.c1 FROM t1,t2 WHERE t1.c1 = t2.c1; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (3), (4); +SELECT * FROM v1; +--error ER_NO_SUCH_TABLE +TRUNCATE v1; +SELECT * FROM v1; +# +LOCK TABLE t1 WRITE; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM v1; +--error ER_TABLE_NOT_LOCKED +TRUNCATE v1; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM v1; +UNLOCK TABLES; +# +LOCK TABLE t1 WRITE, t2 WRITE; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM v1; +--error ER_TABLE_NOT_LOCKED +TRUNCATE v1; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM v1; +UNLOCK TABLES; +# +LOCK TABLE v1 WRITE; +SELECT * FROM v1; +--error ER_TABLE_NOT_LOCKED +TRUNCATE v1; +SELECT * FROM v1; +UNLOCK TABLES; +# +LOCK TABLE t1 WRITE, t2 WRITE, v1 WRITE; +SELECT * FROM v1; +--error ER_TABLE_NOT_LOCKED +TRUNCATE v1; +SELECT * FROM v1; +UNLOCK TABLES; +# +DROP VIEW v1; +DROP TABLE t1, t2; +# +CREATE PROCEDURE p1() SET @a = 5; +--error ER_NO_SUCH_TABLE +TRUNCATE p1; +SHOW CREATE PROCEDURE p1; +DROP PROCEDURE p1; + +--enable_service_connection + +--echo # +--echo # Bug#46452 Crash in MDL, HANDLER OPEN + TRUNCATE TABLE +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 AS SELECT 1 AS f1; + +HANDLER t1 OPEN; +--echo # Here comes the crash. +TRUNCATE t1; + +--echo # Currently TRUNCATE, just like other DDL, implicitly closes +--echo # open HANDLER table. +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +# Cleanup +DROP TABLE t1; + +--echo # +--echo # End of 5.5 tests +--echo # |