MySQL Bugs: #29507: TRUNCATE shows too many rows affected

Bug #29507	TRUNCATE shows too many rows affected
Submitted:	3 Jul 2007 5:58	Modified:	10 Nov 2008 19:00
Reporter:	Jared Sullivan (Silver Quality Contributor)
Status:	Closed
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.0.44	OS:	Microsoft Windows (Vista)
Assigned to:	Timothy Smith	Target Version:
Tags:	v6, truncate, delete, rows effected, qc
Triage:	Triaged: D4 (Minor)

Description:
Hi,

I have a very strange problem when I am deleting rows from table that has 1309 rows. 
When I execute TRUNCATE TABLE command, Query Browser shows 1695 rows effected.

How to repeat:
1. Upload my private script
2. truncate table  `sale-contacts`;

RESULT return 1695 rows, but table has 1309

Further more, if you run truncate command again on empty table, it always returns 1 row
effected, but table is empty, and query should not yeild any results of any type.

Hmm... maybe the row count is just an estimate? InnoDB does not keep and store an accurate
row count of a table like MyISAM does.

DELETE FROM `sale-contacts` -- Works fine, gives accurate count, so InnoDB has the
'brains' to do the math.  

I have tried few different things like updating NULLs to '' and deleting all columns
execpt for PRIMARY, but the 'rows effected' just keeps getting weirder

Congrates on the takeover.

Verified just as described:

mysql> show create table tbiggg;
+--------+----------------------------------------------------------------------
-------------------------------------------------------+
| Table  | Create Table
                                                       |
+--------+----------------------------------------------------------------------
-------------------------------------------------------+
| tbiggg | CREATE TABLE `tbiggg` (
  `c1` char(100) default NULL,
  `c2` char(100) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------
-------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select count(*) from tbiggg;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (15.75 sec)

mysql> update tbiggg set c2='abcdefgh';
Query OK, 2097152 rows affected (3 min 5.13 sec)
Rows matched: 2097152  Changed: 2097152  Warnings: 0

mysql> truncate table tbiggg;
Query OK, 2097243 rows affected (0.25 sec)

mysql> truncate table tbiggg;
Query OK, 1 row affected (0.03 sec)

mysql> truncate table tbiggg;
Query OK, 1 row affected (0.03 sec)

Pushed into InnoDB 5.1 repository.

Note to doc: In InnoDB, the row count is only a rough estimate used by SQL
optimization. InnoDB is now return row count 0 for TRUNCATE operation.

Pushed into 5.1.25-rc

Setting report to Patch queued pending re-push of this patch.

Pushed into 6.0.6-alpha

The push into 6.0.x was a null merge that changed nothing. Resetting report to Patch
Approved pending further push of patch into 6.0.x.

The "parent" report for this set of bugs is Bug#32440.

Merged into 6.0.6-alpha, according to Tim. But the patch has not been pushed into 5.1 yet.

Noted in 6.0.6 changelog.

TRUNCATE TABLE for InnoDB tables returned a count showing too many
rows affected. Now the statement returns 0 for InnoDB tables. 

Setting report to Need Doc Info pending push into 5.1.x.

Hi,

Can we please get the 3-part version number for the 5.1 merge of this fix, so we can
document and close? Thanks!

Tagged changelog entry for 5.1.28 release.

Left in NDI status per discussion with Joro (waiting on decision whether or not to push
to 5.0).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57688

2690 Kristofer Pettersson	2008-11-03
      Bug#29507 TRUNCATE shows to many rows effected
      
      TRUNCATE TABLE for InnoDB tables returned a count showing an approximation
      of the number of rows affected to gain efficiency.
      Now the statement always returns 0 rows affected for clarity.

Added a proposal for a new patch based on discussions with Sergei G. This patch will help
to resolve the regression in bug#40386 "Not flushing query cache after truncate".

The TRUNCATE TABLE specific code in ha_innobase::info must also be reverted to complete
this patch because it breaks the API which dictates that stats.records never should be 0
if the table isn't empty.

Turning over to InnoDB.

Approved on IRC

Sergei,

what value InnoDB can return as the row count?

InnoDB does not know the exact count.

Can it return some huge number?

--Heikki

Heikki, you can return any number, as long as it's not zero.

The rule for table->file->stats.records is (and always was) - it's either exact number of
rows, when HA_STATS_RECORDS_IS_EXACT is set, and the approximate number of rows otherwise.
But even if it's approximate it can be zero only if the table is empty. Optimizer relies
on it.

As for this particular bug - Bug#29507, "TRUNCATE shows too many rows affected" - we've
fixed it, see the patch attached. TRUNCATE was never supposed to return a number of rows,
it's even in the manual. When TRUNCATE worked as DROP+CREATE it never returned number of
rows. The bug was that when TRUNCATE worked via DELETE, we forgot to disable that part of
DELETE that returns the number of affected rows. It's now fixed.

So for TRUNCATE it doesn't matter what number you return, as long as the above invariant
(it's zero only if the table is empty) holds.

Pushed into 6.0.8-alpha 
(revid:kristofer.pettersson@sun.com-20081103130842-axiwbe6lfnmi6mzz) (version source
revid:kristofer.pettersson@sun.com-20081103160635-zv2ux0yweso8lgky) (pib:5)

Pushed into 5.1.30  (revid:kristofer.pettersson@sun.com-20081103130842-axiwbe6lfnmi6mzz)
(version source revid:mattias.jonsson@sun.com-20081104113355-1xg11r9sdp0k6ovj) (pib:5)

Already noted in 5.1.28, 6.0.6 changelogs. Re-closing.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii)
(version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers:
5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl)
(version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers:
5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5)
(version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers:
5.1.31-ndb-6.4.1) (pib:6)