MySQL Bugs: #2709: Affected Rows for ON DUPL. KEY undocumented, perhaps illogical

Bug #2709	Affected Rows for ON DUPL. KEY undocumented, perhaps illogical
Submitted:	11 Feb 2004 11:12	Modified:	18 Oct 2004 18:02
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Two parts:

1) The method by which the return value for mysql_affected_rows() is calculated for INSERT ... ON DUPLICATE KEY UPDATE statements is not clearly documented.

2) Currently, it returns a value calculated like REPLACE (1 for each INSERT plus 1 for each of these which was actually processed as an UPDATE), so that a 5 row INSERT with 2 duplicates returns 7 (5 inserts, 2 updates) rather than 5 (3 inserts, 2 updates).

How to repeat:
n/a

Suggested fix:
1) Document this in the INSERT Syntax chapter (and perhaps in mysql_affected_rows() chapter).

2) Provided Monty approves, change the calculation to return the presumably more logical value.

for five rows with two duplicates affected_rows should be 5: 3 inserts, 2 updates

this is a server issue, not only docs.

subj: bk commit - 4.1 tree (vva:1.1756)

ChangeSet
  1.1756 04/03/04 16:54:10 vva@eagle.mysql.r18.ru +4 -0
  fixed Bug #2709 "Affected rows for ON DUPL.KEY undocumented, perhaps illogical" and
  added commands enable_affected_rows and disable_affected_rows to client/mysqltest.c

Assume the following case:
create table t1(a int primary key, b int);
insert into t1 values(1,1);
insert into t1 values(1,1) on duplicate key b=b+1;

It's better that we return 2 as row count for affected rows as this allows the application to easy know what happened:
0 -> INSERT FAILED
1 -> Row was inserted
2 -> Row was updated

I would like to second the motion for the documentation to be updated to reflect this return value in both the function reference as well as the INSERT statement syntax.  I was bitten by this issue in testing the return results on this function (and the respective query in mysql-client), and initially my program returned it as invalid and rolled back the transaction (which clearly was partially my fault...).  I will add it to the comments on the respective pages on dev.mysql.com, but it would be nice if it was included in the officially released documentation as well, so it could be disseminated in other forms.

However, now that I understand how to interpret the return value, it is logical and does provide more information, and thus is a convenient feature.

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
product(s).

i was created the table with the field NAME
and iam inserting the string into that name field
as given below:

insert into rajesh NAME values(' rajesh 'kumar');
then iam getting the error why so.
reply me immly.

i was created the table with the field NAME
and iam inserting the string into that name field
as given below:

insert into rajesh NAME values(' rajesh 'kumar');
 //that means i am inserting singlequotes along with that text

then iam getting the error why so.what is the solution for this to insert a single quote or double quote.so kindly reply me immly.