Bug: MySQL Merge FormatException with specific primary key string values

[crackalak]

Bug Description

When processing the result of the Merge, the data type of the row identifier is processed incorrectly causing a FormatException.

On investigation, this happens with MySQL versions from v8.0.22 onwards due to changes with how prepared statements are processed.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html

Exception Message:

One of the identified items was in an invalid format.

System.FormatException:
   at MySqlConnector.ColumnReaders.DecimalColumnReader.DoReadValue (MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: /_/src/MySqlConnector/ColumnReaders/DecimalColumnReader.cs:19)
   at MySqlConnector.ColumnReaders.DecimalColumnReader.ReadValue (MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: /_/src/MySqlConnector/ColumnReaders/DecimalColumnReader.cs:12)
   at MySqlConnector.Core.Row.GetValue (MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92: /_/src/MySqlConnector/Core/Row.cs:93)

Schema and Model:

CREATE TABLE IF NOT EXISTS `Item` (
    `id`      char(36) NOT NULL,
    `name`    varchar(64) NULL,
    PRIMARY KEY (`id`)
);

await using var connection = new MySqlConnection(_connectionString);
GlobalConfiguration
        .Setup()
        .UseMySqlConnector();

var items = new List<Item>
{
    new()
    {
         Id = "987e89c2-73be-418e-a634-1af740c41cdf", // bad
         Id = "987e89c", // bad - presumably detected as an exponential number
         Id = "987f89c", // good
        // Id = "f0d8f284-8627-42da-9d17-24d76c72daf6", // good
        Name = "Test"
    }
};

await connection.MergeAllAsync(items);

[Table("Item")]
public record Item
{
    public string Id { get; set; }
    public string Name { get; set; }
}

This results in executing the following sql statement:

INSERT INTO `Item` (`Id`, `Name`)
VALUES(@Id_1, @Name_1)
ON DUPLICATE KEY UPDATE `Id` = @Id_1,
    `Name` = @Name_1;
SELECT COALESCE(@id_1, LAST_INSERT_ID()) AS `Result`, @__RepoDb_OrderColumn_1 AS `OrderColumn`

The usage of COALESCE to get the record id causes an issue with MySQL trying to detect the data type, see repro with mysqlsh below:

MySQL 8.0.21

MySQL 8.0.22

The building of the return value statement is done in the RepoDb.MySqlConnector library here:

RepoDB/RepoDb.MySqlConnector/RepoDb.MySqlConnector/StatementBuilders/MySqlConnectorStatementBuilder.cs

Lines 480 to 487 in a817062

	// Set the return value
	var returnValue = keyColumn != null ? keyColumn.Name.AsParameter(index, DbSetting) : "NULL";
	builder
	.Select()
	.WriteText(
	string.Concat($"COALESCE({returnValue}, LAST_INSERT_ID())", " AS ", "Result".AsQuoted(DbSetting), ","))
	.WriteText(
	string.Concat($"{DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index}", " AS ", "OrderColumn".AsQuoted(DbSetting)));

I'd propose removal of COALESCE usage and instead check IsIdentity when building the return value:

var returnValue = keyColumn != null ? keyColumn.Name.AsParameter(index, DbSetting) : "NULL";

builder
    .Select()
        .WriteText(
            string.Concat(keyColumn?.IsIdentity is true ? $"COALESCE({returnValue}, LAST_INSERT_ID())" : returnValue, " AS ", "Result".AsQuoted(DbSetting), ","))
        .WriteText(
            string.Concat($"{DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index}", " AS ", "OrderColumn".AsQuoted(DbSetting)));

Library Version:

RepoDb v1.13.1 and RepoDb.MySqlConnector v1.13.1

[rhuijben]

I forked this repository on https://github.com/ampscm/repodb
When I try this patch I see other issues in existing testcases in the testsuite.

You can easily test this by creating a PR against this other repository and see if you can handle all the edge cases.

I think a better algorithm is needed for getting all the values to be returned properly. In case of the identity column this could be easy, while in others it might require joining against the just returned data (see the insertall implementations). With the global option on what needs to be returned when things get very complicated to get right.

For now I don't feel safe just committing this PR to my fork.