Bug #58167 COLUMN_DEFAULT should return a valid SQL expression
Submitted: 12 Nov 2010 20:07 Modified: 18 Nov 2010 15:04
Reporter: Max Toro Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.3.5.0 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[12 Nov 2010 20:07] Max Toro
Description:
The COLUMN_DEFAULT field of GetSchema Columns collection should return a valid SQL expression. 

Currently it returns a string representation of the value, e.g. 0 (integer), 0.0 (decimal), hello (varchar). If I build a query to get the values e.g.:
SELECT 0
.. it works for numeric values, but for text values e.g.:
SELECT hello
.. it fails because the value is not quoted.

It is required to make a query to get the default value, in case it is computed, e.g.:
SELECT CURRENT_TIMESTAMP

How to repeat:
1. Call MySqlConnection.GetSchema to get the Columns collection of a table with a text column that has a non-null default value.
2. Get the COLUMN_DEFAULT field.

Suggested fix:
Return text values quoted. e.g. 'hello' instead of hello
[15 Nov 2010 7:08] Tonci Grgin
Hi Max and thanks for your report.

Would you mind:
  o Noting the MySQL server version.
  o Attaching small but complete test case used to repeat the problem.
  o Use newer version of c/NET and see if your problem is still there.
[15 Nov 2010 14:55] Max Toro
- Reproduced with with latest Connector/NET GA version, 6.3.5.0
- MySQL version 5.1.47-community

To reproduce:
1. Create a table with a text column (text, char, varchar) that has a non-null default value, e.g.

CREATE TABLE `test`.`foo`(     `bar` VARCHAR(50) DEFAULT 'hello'   );

2. Get the default value.
DataTable schema = conn.GetSchema("Columns", new string[4] { null, "foo", "bar", null });
foreach (DataRow row in schema.Rows) {                 
   Console.WriteLine(row["COLUMN_DEFAULT"] ?? "").ToString());
}
[17 Nov 2010 23: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/124205

847 Reggie Burnett	2010-11-17
      - return default values for text columns as quoted (bug #58167)
[17 Nov 2010 23:03] Reggie Burnett
Fixed in 6.0.8, 6.1.6, 6.2.5, and 6.3.6+
[18 Nov 2010 15:04] Tony Bedford
An entry has been added to the 6.0.8, 6.1.6, 6.2.5, 6.3.6 changelogs:

Default values returned for text columns were not quoted. This meant that the COLUMN_DEFAULT field of the GetSchema columns collection did not return a valid SQL expression.