Bug #48171 MySqlDataReader.GetSchemaTable() returns 0 in "NumericPrecision" for newdecimal
Submitted: 19 Oct 2009 19:55 Modified: 16 Dec 2009 14:05
Reporter: Andrey Belykh Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:trunk OS:Microsoft Windows
Assigned to: Reggie Burnett CPU Architecture:Any
Triage: D2 (Serious)

[19 Oct 2009 19:55] Andrey Belykh
Description:
MySqlDataReader.GetSchemaTable() returns 0 in "NumericPrecision" for newdecimal column.

Even though I get some number in a "ColumnSize", it seems that currently it is impossible to distinguish the following data types:

decimal(35,2)
decimal(36,2) unsigned

Currently both return 37 as "MaxLength", so it is impossible to calculate original precision.

How to repeat:
using (MySqlConnection cn = new MySqlConnection("DataSource=**;Database=**;UserID=**;Password=**;"))
{
    cn.Open();

    MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug", cn);
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = "CREATE TABLE bug (a decimal(36,2));";
    cmdCreateTable.ExecuteNonQuery();

    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = cn;
    cmd.CommandText = "SELECT * FROM bug";
    MySqlDataReader rd = cmd.ExecuteReader();
    DataTable schemaTable = reader.GetSchemaTable();
    DataRow columnDefinition in schemaTable.Rows[0];
    Console.Out.WriteLine("NumericPrecision is: " + columnDefinition.Field<int?>(SchemaTableColumn.NumericPrecision).ToString());
}

Suggested fix:
Return numeric precision from the column definition.
[16 Nov 2009 9:31] Tonci Grgin
Hi Andrey and thanks for your report.

I believe this is a side-effect of Bug#42980. Can you please state MySQL server version used.
[17 Nov 2009 13:14] Andrey Belykh
MySQL version is 5.1.39-community
[18 Nov 2009 9:44] Tonci Grgin
-

Attachment: Bug48171.jpg (image/jpeg, text), 62.71 KiB.

[18 Nov 2009 9:55] Tonci Grgin
Hi Andrey.

I was able to verify your problem just as described.

But I see two more problems that are out of hands for c/NET team:
  o "NUM flag for DECIMAL column" is fixed only in MySQL server 6.x and 5.5.x
  o lack of support for UNSIGNED types in .NET FW (if you check your datatable you'll see there is no place to mark field type as UNSIGNED)

So I really don't know if your problem can be fixed.

As can be seen in attached image, DataTable.Rows[0].ItemArray 3 (Numeric precision) is not set. Maybe this value can be used to help us distinguish between unsigned and signed decimal column.

Reggie, Wlad?
[18 Nov 2009 9:57] Tonci Grgin
Test case:
    MySqlConnection con = new MySqlConnection();
    con.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=**;Charset=utf8";
    con.Open();
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "drop table if exists bug48171";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "create table bug48171 (id integer NOT NULL AUTO_INCREMENT PRIMARY KEY, DECCol DECIMAL(36,2)) DEFAULT CHARSET UTF8";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "SELECT DECCol FROM bug48171";
    MySqlDataReader rd = cmd.ExecuteReader();
    DataTable dt = rd.GetSchemaTable();
    DataRow dr = dt.Rows[0];
    Console.WriteLine("ColSize :" + dr.Field<int?>SchemaTableColumn.ColumnSize).ToString());
    Console.WriteLine("NumPrec : " + dr.Field<int?>(SchemaTableColumn.NumericPrecision).ToString());
    Console.WriteLine("NumScale: " + dr.Field<int?>(SchemaTableColumn.NumericScale).ToString());
[9 Dec 2009 15:00] Bugs System
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/93337

778 Reggie Burnett	2009-12-09
      - Fixed precision calculation on decimal and newdecimal columns (bug #48171)
[9 Dec 2009 15:05] Bugs System
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/93339

795 Reggie Burnett	2009-12-09 [merge]
      Fixed precision calculation on decimal and newdecimal columns (bug #48171)
[9 Dec 2009 15:09] Bugs System
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/93343

813 Reggie Burnett	2009-12-09 [merge]
      Fixed precision calculation on decimal and newdecimal columns (bug #48171)
[9 Dec 2009 15:10] Bugs System
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/93345

809 Reggie Burnett	2009-12-09 [merge]
      Fixed precision calculation on decimal and newdecimal columns (bug #48171)
[9 Dec 2009 15:11] Reggie Burnett
Fixed in 6.0.6, 6.1.4, 6.2.2, and 6.3+
[16 Dec 2009 14:05] Tony Bedford
An entry has been added to the 6.0.6, 6.1.4, 6.2.2, and 6.3.0 changelogs:

The method MySqlDataReader.GetSchemaTable() returned 0 in the NumericPrecision field for decimal and newdecimal columns.