Bug #48171 MySqlDataReader.GetSchemaTable() returns 0 in "NumericPrecision" for newdecimal

• Submitted: 19 Oct 21:55
• Modified: 18 Nov 10:55
• Reporter: Andrey Belykh
• Status: Verified
• Category: Connector/Net
• Severity: S3 (Non-critical)
• Version: trunk
• OS: Microsoft Windows
• Assigned to: Reggie Burnett
• Triage: D2 (Serious)

[19 Oct 21: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 10: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 14:14] Andrey Belykh

MySQL version is 5.1.39-community

[18 Nov 10:44] Tonci Grgin

-

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

[18 Nov 10: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 10: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());