Bug #36698 ROW_COUNT() is reset inconsistently

• Submitted: 13 May 2008 19:57
• Modified: 20 May 2008 11:02
• Reporter: Scott Noyes
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.60/5.1BK
• OS: Any
• CPU Architecture: Any

[13 May 2008 19:57] Scott Noyes

Description:
ROW_COUNT() is reset to -1 by a SELECT statement, but not by an INSERT statement which fails due to duplicate key constraints.

How to repeat:
CREATE TABLE t1 (id int primary key);

INSERT t1 VALUES (1); -- row count set to 1
SELECT 1; -- row count set to -1
INSERT t1 VALUES (1); -- row count untouched
SELECT ROW_COUNT(); -- Returns -1

TRUNCATE TABLE t1;

INSERT t1 VALUES (1); row count set to 1
INSERT t1 VALUES (1); row count untouched
SELECT ROW_COUNT(); -- Returns 1

Suggested fix:
An INSERT that fails should reset ROW_COUNT() to -1, just like a SELECT statement does, or else a SELECT statement should not reset ROW_COUNT(). 

For consistency, I'd say that the latter is preferable, since that's how functions like LAST_INSERT_ID() behave.

For the particular issue that raised this bug, a workaround is found by handling failed inserts differently, to avoid calling ROW_COUNT() in an ambiguous situation.

This may already have been addressed with bug 21818.