[MDEV-26812] insert ... on duplicate update rows affected is unclear when CLIENT_FOUND_ROWS flag is set Created: 2021-10-13  Updated: 2021-10-14

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.4.17, 10.5.11
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Jon Scharff Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

docker (custom DockerFile): base OS, docker dist Open SUSE Leap 15.2 using zypper and OpenSUSE repo.


 Description   

I am running an insert ... on duplicate update query with the CLIENT_FOUND_ROWS flag set and the results are inconsistent.

With a query targeting a single row where the row exists and is updated, the result is that 2 rows were affected. However, if the row exists and is not updated then the result is that 1 row was affected. My expectation is that, since the CLIENT_FOUND_ROWS flag is set, this result should be consistent.

I would prefer that the result be 1 row affected, but the documentation states that an update reports 2 rows unless the API's CLIENT_FOUND_ROWS flag is set. The documentation does not state what should be expected when the CLIENT_FOUND_ROWS flag is set. It would be nice if this were clearer.

Also, when the CLIENT_FOUND_ROWS flag is not set, it would be helpful to include in the documentation the reasoning behind why an update returns 2 rows. My assumption is that it is to differentiate between an insert and an update. However, I can't imagine the result becomes ambiguous when there may be more than 1 target row (insert ... select ... on duplicate update, etc.)

create table test_insert_update (code varchar(32) not null, a_id bigint unsigned, primary key(code));
 
 
 
insert into test_insert_update values ('test', 2) on duplicate key update a_id = value(a_id); -- 1 row
 
 
 
insert into test_insert_update values ('test', 1) on duplicate key update a_id = value(a_id); -- 2 rows
 
 
 
insert into test_insert_update values ('test', 1) on duplicate key update a_id = value(a_id); -- 1 row

(Originally reported at the MySqlConnnector repo)

 Comments   
Comment by Alice Sherepa [ 2021-10-13 ]

Yes, that's right, with ON DUPLICATE KEY UPDATE the affected-rows value is set to 1 if the row was just inserted and 2 - to demonstrate that the row was updated, not just inserted.

If the row is set to its current value: affected-rows = 0,
but with CLIENT_FOUND_ROWS flag - affected-rows = 1

kb says: (For UPDATE, affected rows is by default the number of rows that were actually changed. If the CLIENT_FOUND_ROWS flag to mysql_real_connect() is specified when connecting to mysqld, affected rows is instead the number of rows matched by the WHERE clause.)

Comment by Jon Scharff [ 2021-10-14 ]

Alice, thanks for the clarification on the reasoning. I still believe that when CLIENT_FOUND_ROWS is set, the result should not depend on whether the update row changes, as this is consistent with other data manipulation statements (INSERT, UPDATE). Is there a reason I would get 2 rows if the row exists and is updated but only 1 row if the row exists and the value is not changed by the update portion of the INSERT ... ON DUPLICATE KEY UPDATE?

Generated at Thu Feb 08 09:48:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.