ExecuteNonQuery returns more rows affected than expected (UseAffectedRows = false) #1057
Description
I am running an insert ... on duplicate update query using MySqlConnector 1.2.1 and MariaDB 10.4.17.
My expectation is that it should only match 1 row for inserting/updating, but ExecuteNonQuery returns 2 rows affected when the row already exists (as was the case in #973).
If I call ExecuteNonQuery a second time with the same CommandText and parameter values, it returns 1 row affected as expected. With an update command, the first call also returns 1 row affected as expected.
The target table has foreign keys which I believe might contribute to the unexpected result.
Here is a repro.
create table test_a (id serial, primary key(id) );
create table test_b (code varchar(32) not null, a_id bigint unsigned, primary key(code), foreign key (a_id) references test_a (id));
insert into test_a values (null);
insert into test_b values ('test', last_insert_id());
insert into test_a values (null);void InsertOrUpdate() {
using IDbConnection conn = null; //"User Id=**;Server=**;Port=3306;Database=**;Treat Tiny As Boolean=False"
conn.Open();
using IDbTransaction trans = conn.BeginTransaction();
using IDbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "insert into test_b values (?, ?) on duplicate key update a_id = value(a_id)";
IDbDataParameter p = cmd.CreateParameter();
p.ParameterName = "code";
p.DbType = DbType.String;
p.Value = "test";
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "a_id";
p.DbType = DbType.Int64;
p.Value = 2;
cmd.Parameters.Add(p);
Console.WriteLine($"First execution: {cmd.ExecuteNonQuery()}"); // 2
Console.WriteLine($"Second execution: {cmd.ExecuteNonQuery()}"); // 1
}
void Update() {
using IDbConnection conn = null; //"User Id=**;Server=**;Port=3306;Database=**;Treat Tiny As Boolean=False"
conn.Open();
using IDbTransaction trans = conn.BeginTransaction();
using IDbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "update test_b set a_id = ? where code = ?";
IDbDataParameter p = cmd.CreateParameter();
p.ParameterName = "a_id";
p.DbType = DbType.Int64;
p.Value = 2;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "code";
p.DbType = DbType.String;
p.Value = "test";
cmd.Parameters.Add(p);
Console.WriteLine(conn.ConnectionString);
Console.WriteLine($"First execution: {cmd.ExecuteNonQuery()}"); // 1
Console.WriteLine($"Second execution: {cmd.ExecuteNonQuery()}"); // 1
}