1# 2# Test of truncate 3# 4 5create table t1 (a integer, b integer,c1 CHAR(10)); 6insert into t1 (a) values (1),(2); 7truncate table t1; 8select count(*) from t1; 9insert into t1 values(1,2,"test"); 10select count(*) from t1; 11delete from t1; 12select * from t1; 13drop table t1; 14# The following should fail 15--error 1146 16select count(*) from t1; 17create temporary table t1 (n int); 18insert into t1 values (1),(2),(3); 19truncate table t1; 20select * from t1; 21drop table t1; 22--error 1146 23truncate non_existing_table; 24 25# 26# test autoincrement with TRUNCATE; verifying difference with DELETE 27# 28 29create table t1 (a integer auto_increment primary key); 30insert into t1 (a) values (NULL),(NULL); 31truncate table t1; 32insert into t1 (a) values (NULL),(NULL); 33SELECT * from t1; 34delete from t1; 35insert into t1 (a) values (NULL),(NULL); 36SELECT * from t1; 37drop table t1; 38 39# Verifying that temp tables are handled the same way 40 41create temporary table t1 (a integer auto_increment primary key); 42insert into t1 (a) values (NULL),(NULL); 43truncate table t1; 44insert into t1 (a) values (NULL),(NULL); 45SELECT * from t1; 46delete from t1; 47insert into t1 (a) values (NULL),(NULL); 48SELECT * from t1; 49drop table t1; 50 51# End of 4.1 tests 52 53# Test for Bug#5507 "TRUNCATE should work with views" 54# 55# when it'll be fixed, the error should become 1347 56# (test.v1' is not of type 'BASE TABLE') 57# 58 59create table t1 (s1 int); 60insert into t1 (s1) values (1), (2), (3), (4), (5); 61create view v1 as select * from t1; 62--error 1146 63truncate table v1; 64drop view v1; 65drop table t1; 66 67# End of 5.0 tests 68 69--echo # 70--echo # Bug#20667 - Truncate table fails for a write locked table 71--echo # 72CREATE TABLE t1 (c1 INT); 73LOCK TABLE t1 WRITE; 74INSERT INTO t1 VALUES (1); 75SELECT * FROM t1; 76TRUNCATE TABLE t1; 77SELECT * FROM t1; 78UNLOCK TABLES; 79# 80LOCK TABLE t1 READ; 81--error ER_TABLE_NOT_LOCKED_FOR_WRITE 82TRUNCATE TABLE t1; 83UNLOCK TABLES; 84# 85CREATE TABLE t2 (c1 INT); 86LOCK TABLE t2 WRITE; 87--error ER_TABLE_NOT_LOCKED 88TRUNCATE TABLE t1; 89UNLOCK TABLES; 90# 91CREATE VIEW v1 AS SELECT t1.c1 FROM t1,t2 WHERE t1.c1 = t2.c1; 92INSERT INTO t1 VALUES (1), (2), (3); 93INSERT INTO t2 VALUES (1), (3), (4); 94SELECT * FROM v1; 95--error ER_NO_SUCH_TABLE 96TRUNCATE v1; 97SELECT * FROM v1; 98# 99LOCK TABLE t1 WRITE; 100--error ER_TABLE_NOT_LOCKED 101SELECT * FROM v1; 102--error ER_TABLE_NOT_LOCKED 103TRUNCATE v1; 104--error ER_TABLE_NOT_LOCKED 105SELECT * FROM v1; 106UNLOCK TABLES; 107# 108LOCK TABLE t1 WRITE, t2 WRITE; 109--error ER_TABLE_NOT_LOCKED 110SELECT * FROM v1; 111--error ER_TABLE_NOT_LOCKED 112TRUNCATE v1; 113--error ER_TABLE_NOT_LOCKED 114SELECT * FROM v1; 115UNLOCK TABLES; 116# 117LOCK TABLE v1 WRITE; 118SELECT * FROM v1; 119--error ER_TABLE_NOT_LOCKED 120TRUNCATE v1; 121SELECT * FROM v1; 122UNLOCK TABLES; 123# 124LOCK TABLE t1 WRITE, t2 WRITE, v1 WRITE; 125SELECT * FROM v1; 126--error ER_TABLE_NOT_LOCKED 127TRUNCATE v1; 128SELECT * FROM v1; 129UNLOCK TABLES; 130# 131DROP VIEW v1; 132DROP TABLE t1, t2; 133# 134CREATE PROCEDURE p1() SET @a = 5; 135--error ER_NO_SUCH_TABLE 136TRUNCATE p1; 137SHOW CREATE PROCEDURE p1; 138DROP PROCEDURE p1; 139 140--echo # 141--echo # Bug#46452 Crash in MDL, HANDLER OPEN + TRUNCATE TABLE 142--echo # 143--disable_warnings 144DROP TABLE IF EXISTS t1; 145--enable_warnings 146 147CREATE TABLE t1 AS SELECT 1 AS f1; 148 149HANDLER t1 OPEN; 150--echo # Here comes the crash. 151TRUNCATE t1; 152 153--echo # Currently TRUNCATE, just like other DDL, implicitly closes 154--echo # open HANDLER table. 155--error ER_UNKNOWN_TABLE 156HANDLER t1 READ FIRST; 157 158# Cleanup 159DROP TABLE t1; 160 161--echo # 162--echo # End of 5.5 tests 163--echo # 164