MySQL Bugs: #48100: Impossible to retrieve decimal value if it doesn't fit into .Net System.Decimal

Bug #48100	Impossible to retrieve decimal value if it doesn't fit into .Net System.Decimal
Submitted:	15 Oct 2009 20:18	Modified:	5 Nov 2009 15:49
Reporter:	Andrey Belykh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.1.2.0	OS:	Windows
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Impossible to retrieve decimal value if it doesn't fit into .Net System.Decimal

How to repeat:
1) Create a table like that:
CREATE TABLE long_number (a decimal(36,2));
insert into long_number values (9999999999999999999999999999999999.99);

2) Try to read the data from this table:
....
return mySqlReader[columnIndex];
....

This will throw OverflowException (as expected). Well, at least that's what MS and Oracle providers do :)

It seems that currently there's no way of getting the value from the table.

Suggested fix:
Do the same thing what MS or Oracle does. Introduce a new type MySql.Data.Types.MySqlDecimal and add a new method MySqlDataReader.GetMySqlDecimal(). I should be able to use MySqlDecimal.ToDouble() and MySqlDecimal.ToString().

FYI: 
- MS has SqlDecimal data type and method SqlDataReader.GetSqlDecimal
- Oracle has OracleDecimal data type and method OracleDataReader.GetOracleDecimal

Hi Andrey and thanks for your report.

True, mscorlib throws "Value was either too large or too small for a Decimal."
Verified as described. Please lower the severity as this is actually a feature request (S4).

Test case:
            using (MySqlConnection cn = new MySqlConnection("DataSource=**;Database=**;UserID=**;Password=**;PORT=**;logging=True;charset=utf8"))
            {
                cn.Open();
                try
                {
                    MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug48100", cn);
                    cmdCreateTable.ExecuteNonQuery();
                    cmdCreateTable.CommandText = "CREATE TABLE bug48100 (a decimal(36,2));";
                    cmdCreateTable.ExecuteNonQuery();
                    cmdCreateTable.CommandText = "INSERT INTO bug48100 VALUES (9999999999999999999999999999999999.99);";
                    cmdCreateTable.ExecuteNonQuery();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = cn;

                    cmd.CommandText = "SELECT * FROM bug48100";
                    MySqlDataReader rd = cmd.ExecuteReader();
                    rd.Read();
                    Console.Out.WriteLine("Result is: " + rd[0]);
                }
                catch (Exception ex)
                {
                    Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
                    throw;
                }
            }

I do agree that the proposed solution sounds like a feature request.

However, what I am only interested in is getting the value. Making MySqlReader.GetDouble() to not throw would work for my purpose. I would like to leave the severity at S2 unless you have a workaround.

Will using a CAST AS CHAR(N) like below work for you as workaround?

MySqlCommand cmd = new MySqlCommand("select a from long_number", conn);
try
{
   decimal dec = (decimal)cmd.ExecuteScalar();
}
catch (OverflowException)
{
  cmd.CommandText = "select cast(a as char(38)) from long_number";
  string val = (string)cmd.ExecuteScalar();
  double dbl = Convert.ToDouble(val);
}

Thanks for your response.

In short, no, it's not a good workaround.

We will mention this in the documentation and refer customers to this bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88647

772 Reggie Burnett	2009-10-29
      - now exposing the MySqlDecimal type along with GetMySqlDecimal methods on data reader (bug #48100)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88649

784 Reggie Burnett	2009-10-29 [merge]
      - now exposing the MySqlDecimal type along with GetMySqlDecimal methods on data reader (bug #48100)

Fixed in 6.0.5, 6.1.3, and 6.2.1+

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88653

793 Reggie Burnett	2009-10-29 [merge]
      - now exposing the MySqlDecimal type along with GetMySqlDecimal methods on data reader (bug #48100)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88654

792 Reggie Burnett	2009-10-29 [merge]
      - now exposing the MySqlDecimal type along with GetMySqlDecimal methods on data reader (bug #48100)

An entry has been added to the 6.0.5, 6.1.3 and 6.2.1 changelogs:

It was not possible to retrieve a value from a MySQL server table, if the value was larger than that supported by the .NET type System.Decimal.

MySQL Connector/NET was changed to expose the MySqlDecimal type, along with the supporting method GetMySqlDecimal.