BulkMerge failing for tables with decimal columns

[JanSiblik]

Description

BulkMerge is failing with an exception when trying to update a decimal column that contains mostly (but not exclusively) whole numbers. The error itself is being thrown by MySQL server:
Error Code: 1264. Out of range value for column 'DecimalColumnName' at row 1

But the problem seems to be in the way the update query is actually generated by the BulkMerge extension. Consider having a table SampleValuesTable(IdColumnName,IntegerColumnName, DecimalColumnName, StringColumnName) and trying to update it using BulkMerge extension - the resulting query (as seen in the MySQL general_log file) would be as follows:

UPDATE  `SampleValuesTable` AS DestinationTable
INNER JOIN (
		  SELECT 1 AS `IdColumnName`, 1 AS `IntegerColumnName`,  10 AS `DecimalColumnName`, 'sometext' AS `StringColumnName`, 0 AS ZZZ_Index
UNION ALL SELECT 2 AS `IdColumnName`, 5 AS `IntegerColumnName`,  10 AS `DecimalColumnName`, 'sometext' AS `StringColumnName`, 1 AS ZZZ_Index
UNION ALL SELECT 3 AS `IdColumnName`, 10 AS `IntegerColumnName`, 1.0 AS `DecimalColumnName`, 'sometext' AS `StringColumnName`, 2 AS ZZZ_Index
) AS StagingTable
ON DestinationTable.`IdColumnName` = StagingTable.`IdColumnName`
SET     DestinationTable.`IntegerColumnName` = StagingTable.`IntegerColumnName`
		, DestinationTable.`DecimalColumnName` = StagingTable.`DecimalColumnName`
        , DestinationTable.`StringColumnName` = StagingTable.`StringColumnName`;

Notice the decimal values of the first two rows are actually whole numbers and are represented without decimal dot. Which is THE issue, really - if the decimal values were all whole numbers or all decimal numbers, the query would execute OK. Even if the first row of the StagingTable would be decimal and all the remaining rows whole numbers, there would be no error. But MySQL cannot (understandably) infer the type of untyped whole number being selected as DecimalColumnName and apparently it has trouble re-typing it when performing multiple unions.

Proposed solution
Considering the BulkMege extension has the information about the entity being merged, namely it knows the type of 'DecimalColumnName' IS decimal, it could prevent this error easily by generating the decimal values in the resulting query with the decimal dot.
Alternatively it could use CAST() to enforce the data type, which would also work for floating point data types (haven't tried but I suspect it would have the same issue).
It would actually be enough to do this for the first row in the StagingTable, since the type of the further rows in the UNION ALL seems to be inferred from the first one.

MySql.Data.MySqlClient.MySqlException: Out of range value for column 'DecimalColumnName' at row 1
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at .(DbCommand , BulkOperation )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.EntityFramework.Extensions.EntityBulkOperation`1.BulkMerge()

Further technical details

• EF version: 6.0.0.0
• EF Extensions version: Z.EntityFramework.Extensions, Version=3.19.0.0,
• Database Provider: MySQL 8.0.17-commercial

[JonathanMagnan]

Hello @JanSiblik ,

Are you using MariaDB? If yes, which version?

Today we got a very similar issue but with a MediumBlob column. Everything was working with the v10.2.x but it was not working anymore starting from v10.3.x

We will create an issue on their side to know more about it but I believe, we will not have choice and go with your option #2 and use a CAST.

We will make a decision probably tomorrow to check if we should be pro-active and already do the fix on our side since I'm not sure if their team will try to fix it or not.

Please let me know if it's MariaDB, perhaps your is different.

[JanSiblik]

Hello @JonathanMagnan,

Thank you for swift reply. No, we're not using MariaDB, we're on commercial MySQL Enterprise Server, v8.0.17
But I would imagine in this specific case, the issue could be the same (and I think the behaviour of the DB engine is in order, as the DB cannot magically infer the specific type from the query)

[JonathanMagnan]

Great,

You might be right, I remember vaguely an issue related to v8.x that was not part of the previous version.

We will look more deeply at it then start making the fix to cast the column type.

[tamys]

Hi. Is that solved? If not is there a workaround? I have the same problem with efcore 3

[JonathanMagnan]

Hello @tamys ,

We will look at it and revisit the history of this JIRA on our side.

[tamys]

@JonathanMagnan Thank you, The only workaround that worked, and in my case is acceptable but is not a solution, is when the first item of the batch i'm trying to bulkmerge is whole number , i'm adding a penny to force the whole number to become decimal

[JonathanMagnan]

Hello @tamys ,

We indeed probably already been able to reproduce it but it seems my developer currently cannot anymore.

He is asking if you could give him what is your sql-mode such as:

sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

Could you also confirm which version of MySql.Data.EntityFramework and MySql.Data are you using?

[tamys]

Hi @JonathanMagnan
sql mode is "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
Sorry forgot to mention i'm using pomelo 3.2.3

[tamys]

any update on this?

[JonathanMagnan]

Hello @tamys ,

Sorry for the long waiting, I indeed forget to answer you.

We currently have a fix under testing.

We don't really like our fix but that's so far the only one we find out.

The issue starts with the v8.x when an UPDATE statement uses a derived table. It looks that the decimal doesn't keep correctly his type in this case so the only solution we found is forcing a CAST.

A new version with this fix should be released next week.

[JonathanMagnan]

Hello @tamys , @JanSiblik

The v5.1.4 has been finally released.

It should now work with MySQL v8.x

If you still have some issues, just let me know which column type is causing it and we will apply the same fix.

Best Regards,

Jon

[tamys]

Thanks @JonathanMagnan
I'll check it and come back if any issues arise

[JonathanMagnan]

Awesome @tamys !

I'll be looking forward to hearing from you,

Jon