MySQL Bugs: #115168: MySqlDataReader.Get<Type> may not return the correct value.

Bug #115168	MySqlDataReader.Get<Type> may not return the correct value.
Submitted:	29 May 2024 20:17	Modified:	13 Dec 2024 23:31
Reporter:	David Ellingsworth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The Get<Type> methods of MySqlDataReader should not use Convert.To<Type> without a locale as the behavior is undefined.

How to repeat:
1. Create a table with a string/text column.
2. Change the culture to the invariant culture using CultureInfo.CurrentCulture = CultureInfo.InvariantCulture.
3. Insert a decimal value into the column using a parameterized query with a Decimal parameter containing the value 12.3.
4. Change the locale of the application to de-DE using CutureInfo.CurrentCulture = CultureInfo.GetCulture("de-DE");
5. Execute a query without an explicit cast to retrieve the value of the column.
6. Observe that calling MySqlDataReader.GetDecimal() for the column returns a decimal value of 123 instead of 12.3.

This fails because the query executed in step 5 returns a string value. Calling MySqlDataReader.GetDecimal() in step 6 sees that the column is not a decimal type and subsequently calls Convert.ToDecimal on the string returned without a locale specified.

This issue affects Decimal, Double, and Float alike.

Suggested fix:
If a conversion is necessary, these methods should either attempt a direct cast and throw an InvalidCastException or attempt to convert the value according to the implicit conversion rules used by the MySql server. Using Convert.To<Type> or Convert.ChangeType may be used if a locale is specified and it will result in appropriate implicit conversion. If necessary, tests should verify that these methods return the expected value for various database collations.

Hello David,

Thank you for the report.
Could you please provide a test case(snippet of C/NET code) to reproduce this issue at our end?

Regards,
Ashwini Patil

Assuming a connection and table have already been created, a simple test would be as follows. Note: None of this has been syntax or compile checked.

decimal expectedValue = 12.3m;

// Set the current locale to the invariant culture.
CultureInfo.CurrentCulture = CultureInfo.InvariantCulture;

// Insert the decimal value into the database.
using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "Insert Into TestTable(TextColumn) Values (@pDateTime)";

    var pDateTime = cmd.CreateDbParameter();

    pDateTime.ParameterName = "@pDateTime";
    pDateTime.DbType = DbType.Decimal;
    pDateTime.Value = expectedValue;

    cmd.Parameters.Add(pDateTime);
    cmd.ExecuteNonQuery();
}

// Set the current locale to de-DE before retrieving the value to simulate performing
// the query on a computer in another region.
CultureInfo.CurrentCulture = CultureInfo.GetCulture("de-DE");

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "Select TextColumn from TestTable";

    using (var reader = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
           decimal dbValue = reader.GetDecimal("TextColumn");

           Assert.AreEqual(expectedValue, dbValue);
        }
    }
}

Hello David,

Thank you for the details.
Verified as described.

Regards,
Ashwini Patil

Posted by developer:
 
This is not a bug, the driver converts the string value from the column using the current Culture of the system, the driver is not in charge of determining the Culture or the decimal separator to be used.