Bug #20625 TRUNCATE TABLE returns wrong affected rows counts
Submitted: 22 Jun 2006 11:56 Modified: 18 Aug 2006 18:56
Reporter: Hartmut Holzgraefe Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0bk/5.1BK OS:Linux (linux)
Assigned to: CPU Architecture:Any

[22 Jun 2006 11:56] Hartmut Holzgraefe
Description:
TRUNCATE TABLE gives different "affected rows" results depending on the table handler.
For a one million row test table i get a result of zero affected rows for MyISAM (which is
sort of OK as a DROP/CREATE will not return an affected row count either), but for an
InnoDB table with 1.000.000 rows TRUNCATE TABLE returns 1.000.329 affected rows.
The same happens with Cluster tables, too.

How to repeat:
 -- create a table with 10 rows --
 create table t1 (i int) engine=myisam;
 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);

 -- use self join to create a 2nd table of 10*10*10 = 1000 rows --
 create table engine=myisam t2 select a1.i*100+a2.i*10+a3.i as i from t1 a1, t1 a2, t1 a3;

 -- another self join creates a 1000*1000 = 1.000.000 row table --
 create table engine=myisam t3 select a1.i*1000+a2.i as i from t2 a1, t2 a2;

 
 create table t4 (i int, v varchar(255)) engine=innodb;

 insert into t4 select i, md5(i) as v from t3;
 -- Query OK, 100000 rows affected 

 truncate table t4;
 -- Query OK, 1000329 rows affected

 truncate table t3;
 -- Query OK, 0 rows affected

Suggested fix:
.
[22 Jun 2006 13:04] MySQL Verification Team
Thank you for the bug report.

create table t1 (i int) engine=myisam;
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
create table  t2 engine=myisam select a1.i*100+a2.i*10+a3.i as i from t1 a1, t1 a2, t1 a3;
create table  t3 engine=myisam select a1.i*1000+a2.i as i from t2 a1, t2 a2;
create table t4 (i int, v varchar(255)) engine=innodb;
insert into t4 select i, md5(i) as v from t3;
select version();
truncate table t4;
truncate table t3;
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.23-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> truncate table t4;
Query OK, 1000357 rows affected (0.09 sec)

mysql> truncate table t3;
Query OK, 0 rows affected (0.01 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> truncate table t4;
Query OK, 1000357 rows affected (0.09 sec)

mysql> truncate table t3;
Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.01 sec)

mysql> truncate table t4;
Query OK, 1000000 rows affected (44.89 sec)

mysql> truncate table t3;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.27-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> truncate table t4;
Query OK, 1000000 rows affected (6 min 58.82 sec)

mysql> truncate table t3;
Query OK, 0 rows affected (0.01 sec)

mysql>
[29 Jun 2006 13:32] Dean Swift
Observed functionality is arguably correct because InnoDB removes rows from a file which is shared by all tables whereas MyISAM re-initializes files for each table. For cluster, functionality of ALTER TABLE returns different row counts for version 5.0 and 5.1. Again, this is arguably correct due to differences in structure and functionality.
[29 Jun 2006 20:44] Hartmut Holzgraefe
> Observed functionality is arguably correct ...

i can live with some handlers returning zero while others
return the actual row count (although i'd prefer to see
zero being returned in all cases for consistency), but
InnoDB and cluster obviously return numbers not really
related to the actual table size at all. They should either
return the correct count or just zero, too.
[18 Aug 2006 18:56] Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See http://dev.mysql.com/doc/refman/5.0/en/truncate.html

"TRUNCATE TABLE  differs from DELETE in the following ways: ... The number of
deleted rows is not returned."