truncate.test 1.55 KB
Newer Older
1 2 3
#
# Test of truncate
#
4
--disable_warnings
5
drop table if exists t1;
6 7
--enable_warnings

8
create table t1 (a integer, b integer,c1 CHAR(10));
9
insert into t1 (a) values (1),(2);
monty@donna.mysql.com's avatar
monty@donna.mysql.com committed
10
truncate table t1;
11 12 13
select count(*) from t1;
insert into t1 values(1,2,"test");
select count(*) from t1;
14 15
delete from t1;
select * from t1;
16 17
drop table t1;
# The following should fail
18 19
--error 1146
select count(*) from t1;
20 21 22 23 24
create temporary table t1 (n int);
insert into t1 values (1),(2),(3);
truncate table t1;
select * from t1;
drop table t1;
25 26
--error 1146
truncate non_existing_table;
27 28

#
29
# test autoincrement with TRUNCATE; verifying difference with DELETE
30 31 32 33 34 35 36
#

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;
37 38 39
delete from t1;
insert into t1 (a) values (NULL),(NULL);
SELECT * from t1;
40 41
drop table t1;

42 43 44 45 46 47 48 49 50 51 52
# 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;
53 54

# End of 4.1 tests
55 56

# Test for Bug#5507 "TRUNCATE should work with views"
57 58 59 60
#
# when it'll be fixed, the error should become 1347
# (test.v1' is not BASE TABLE)
#
61 62 63 64

create table t1 (s1 int); 
insert into t1 (s1) values (1), (2), (3), (4), (5);
create view v1 as select * from t1;
65
--error 1146
66
truncate table v1;
67 68
drop view v1;
drop table t1;
69 70

# End of 5.0 tests
71