Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26812

insert ... on duplicate update rows affected is unclear when CLIENT_FOUND_ROWS flag is set

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.4.17, 10.5.11
    • Fix Version/s: None
    • 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)

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            jon_s Jon Scharff
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.