Edit File: truncate.test
# # Test of truncate # --disable_warnings drop table if exists t1, t2; --enable_warnings 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 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 # 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; --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 # End of 6.0 tests
Back to File Manager