MySQL Bugs: #43289: TRUNCATE TABLE always returns (0 row(s) affected)

Bug #43289	TRUNCATE TABLE always returns (0 row(s) affected)
Submitted:	1 Mar 2009 12:52	Modified:	7 Aug 2009 19:30
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
TRUNCATE TABLE always returns (0 row(s) affected) no matter if table is empty or not.

How to repeat:
truncate table ...

Suggested fix:
I know that the server cannot know how many rows were affected as rows are not deleted one-by-one with this command.  But the incorrect information should not be returned! Then better only return 'done' or 'table was truncated'

I'd say this is not a bug. Manual (http://dev.mysql.com/doc/refman/5.1/en/truncate.html) clearly says:

"Truncation operations do not return the number of deleted rows."

DROP TABLE also do not return number of rows the table had...

This is a reasonable feature request though.

I think I should clarify: this passage from docs "Truncation operations do not return the number of deleted rows." is wrong, because the server DOES return information the number of deleted/affected rows - and the information returned is wrong!

So both the server message and the documentation is wrong, basically!

But an ok packet alwaya contains a field for "affected rows", there's no way _not to send_ affected rows. Or you're suggesting to change the protocol ?

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#OK_Packet

Basically "0 (*zero*) rows" and "NULL (*unknown*) rows" is not the same.  That is my point!

As a user reporting an issue I do not think I should need to comment on internals (such as protocol). I do not have much clue about possible other implications of such change either.

It is a minor issue of course and if very fundamental changes are required then of course the result can be considered not worth the effort (at least for the time being).

But I think docs should make it clear that for specific statements (DROP TABLE, TRUNCATE TABLE) '0 rows' is always returned.  

Currently docs say that *no information* is provided - the truth is rather that as no information of the kind is available it will default to return '0 rows' (and that is *information* - not *no information*). 

It is not obvious that '0 rows' should be understood as 'no information'.

All is detail of course. Once you know this particularity it is no buig problem.  If you don't know it may confuse ('ouch .. I dropped the wrong table')

As you can see from the URL above there's a 1-8 number in the ok packet for the number of affected rows. There's no way to put NULL in there without changing the protocol.

I'll change this bug report to the "Documentation" category, as you said the documentation is (or may be) confusing.

OK, looks like all these just should be properly documented.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated TRUNCATE section to indicate that the usual result is "0 rows affected," and that this should be interpreted as "no information."