MySQL Bugs: #58167: COLUMN_DEFAULT should return a valid SQL expression

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:	Windows
Assigned to:		CPU Architecture:	Any

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

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.

- 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());
}

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)

Fixed in 6.0.8, 6.1.6, 6.2.5, and 6.3.6+

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.