Bug #27747 database metadata doesn't return sufficient column default info
Submitted: 11 Apr 2007 4:52 Modified: 24 Oct 2007 17:59
Reporter: Nick Griffiths Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux
Assigned to: Sergei Glukhov CPU Architecture:Any

[11 Apr 2007 4:52] Nick Griffiths
Description:
returning column metadata from DatabaseMetaData.getColumns can't distinguish between a column with no default value and a string column with a default of '' (empty string).  Both columns return an empty string through the metadata api.

How to repeat:
table definition:

create table testtable (
  fun bigint not null,
  time varchar(50) default ''
)

java snippet:
// ... get metadata for columns in table

String funDefaultValue = null;
String timeDefaultValue = null;

while (mdrs.next()) {
  String colName = mdrs.getString("COLUMN_NAME");

  if ("fun".equals(colName)) {
      funDefaultValue = mdrs.getString("COLUMN_DEF");
  } else {
      timeDefaultValue = mdrs.getString("COLUMN_DEF");
  }
}

funDefaultValue and timeDefaultValue are both '', but no default is defined for funDefaultValue.

Suggested fix:
shouldn't the case where no default is specifed return null, rather than an empty string (as well as which, that makes little sense for a non string character)?
[11 Apr 2007 5:53] Mark Matthews
Not actually a bug with the JDBC driver, but with the server, in that when the JDBC driver uses "SHOW COLUMNS" to get the data, it's returned the way you're reporting.

If you're using MySQL-5.0 or newer, you could use "useInformationSchema=true" in your JDBC URL properties, and the driver will use the INFORMATION_SCHEMA instead, which returns this information correctly. 

Unfortunately, until MySQL-5.1, the INFORMATION_SCHEMA doesn't contain enough information to fully-implement any of the foreign-key related methods in DatabaseMetadata (they'll be missing the restrict/cascade constraints), so be aware of that.

(thus I'm changing the category to MySQL Server INFORMATION_SCHEMA, since "SHOW COLUMNS" needs to be fixed to be consistent with INFORMATION_SCHEMA).
[11 Apr 2007 6:07] Nick Griffiths
great, that should hold me over :)
[11 Apr 2007 9:56] Sveta Smirnova
Thank you for the report.

Verified as described.

Should be feature request, though.
[18 Apr 2007 13:38] 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/24774

ChangeSet@1.2456, 2007-04-18 18:35:22+05:00, gluh@mysql.com +16 -0
  Bug#27747 database metadata doesn't return sufficient column default info
[8 Jun 2007 10:41] Alexander Barkov
The patch http://lists.mysql.com/commits/24774 looks ok.

But I suggest to share code between the "SHOW CREATE TABLE"
and the "SHOW COLUMNS" routines.

Consider moving this code into a separate function,
then reuse it for both "SHOW" commands:

   /*
      Again we are using CURRENT_TIMESTAMP instead of NOW because it is
      more standard
    */
    has_now_default= table->timestamp_field == field &&
                     field->unireg_check != Field::TIMESTAMP_UN_FIELD;

    has_default= (field->type() != FIELD_TYPE_BLOB &&
                  !(field->flags & NO_DEFAULT_VALUE_FLAG) &&
                  field->unireg_check != Field::NEXT_NUMBER &&
                  !((thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40))
                    && has_now_default));

    if (has_default)
    {
      packet->append(STRING_WITH_LEN(" default "));
      if (has_now_default)
        packet->append(STRING_WITH_LEN("CURRENT_TIMESTAMP"));
      else if (!field->is_null())
      {                                             // Not null by default
        type.set(tmp, sizeof(tmp), field->charset());
        field->val_str(&type);
        if (type.length())
        {
          String def_val;
          uint dummy_errors;
          /* convert to system_charset_info == utf8 */
          def_val.copy(type.ptr(), type.length(), field->charset(),
                       system_charset_info, &dummy_errors);
          append_unescaped(packet, def_val.ptr(), def_val.length());    
       }
        else
          packet->append(STRING_WITH_LEN("''"));
      }
      else if (field->maybe_null())
        packet->append(STRING_WITH_LEN("NULL"));    // Null as default
      else
        packet->append(tmp);
    }

The new function prototype could be:

  bool store_default_value(Field *field, String *str);

Or it can be a Field class method:

  bool Field::store_default_value(String *str);
[29 Jun 2007 23:41] Peter Gulutzan
Regarding the 2007-04-11 comment
"(thus I'm changing the category to MySQL Server INFORMATION_SCHEMA,
since "SHOW COLUMNS" needs to be fixed to be consistent with
INFORMATION_SCHEMA) ..."
This fix won't ensure that SHOW COLUMNS "shows" all that one can
find out via information_schema. Examples:
create table t1 (s1 varbinary(5) default 0x00);
 show columns in t1;
 select hex(column_default)
 from information_schema.columns
 where table_name='t1';
create table t2 (s1 char(4) default 'NULL');
 show columns in t2;
 select column_default,ifnull(column_default,'!')
 from information_schema.columns
 where table_name='t2';
create table t3 (s1 varchar(5) default '  '); 
 show columns in t3;
 select length(column_default)
 from information_schema.columns
 where table_name='t3';
But generally speaking the information will be available to
connectors even if SHOW doesn't display it, and it will be
possible to distinguish between NULL and ''.
[12 Sep 2007 7:25] 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/34076

ChangeSet@1.2524, 2007-09-12 12:21:11+05:00, gluh@mysql.com +17 -0
  Bug#27747 database metadata doesn't return sufficient column default info
  added get_field_default_value() function which obtains default value from the field
  (used in store_create_info() & get_schema_column_record() functions)
[13 Sep 2007 9:13] Alexander Barkov
The patch http://lists.mysql.com/commits/34076 is ok to push
[20 Sep 2007 9:01] 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/34417

ChangeSet@1.2529, 2007-09-20 13:54:46+05:00, gluh@mysql.com +17 -0
  Bug#27747 database metadata doesn't return sufficient column default info
  added get_field_default_value() function which obtains default value from the field
  (used in store_create_info() & get_schema_column_record() functions)
[24 Sep 2007 8:31] Bugs System
Pushed into 5.0.50
[24 Sep 2007 8:35] Bugs System
Pushed into 5.1.23-beta
[24 Oct 2007 17:59] Paul DuBois
Noted in 5.0.50, 5.1.23 changelogs.

SHOW COLUMNS returned NULL instead of the empty string for the
Default value of columns that had no default specified.
[12 May 2012 16:21] Paul DuBois
Correction: The Default column *now* shows NULL for columns that have no
DEFAULT clause in the column definition.