Description:
Hello,
When you call column_info() on a table that contains date, time or timestamp columns, a wrong value is returned in the DATA_TYPE column. The value returned for date columns is SQL_TYPE_DATE which is not used anywhere else in the code (and in DBI's source). type_info_all() returns SQL_DATE for the same column type, which makes matching the data from type_info_all() and column_info() impossible for those column types.
How to repeat:
my $dbh = DBI->connect('DBI:mysql:database=test');
my @type_info = @{$dbh->type_info_all()};
my $sql_col = $type_info[0]->{DATA_TYPE};
my $name_col = $type_info[0]->{TYPE_NAME};
my $mysql_col = $type_info[0]->{mysql_native_type};
foreach my $type (@type_info[1..$#type_info]) {
my $sql_value = $type->[$sql_col];
my $mysql_value = $type->[$mysql_col];
my $name_value = $type->[$name_col];
print "DATA_TYPE: $sql_value; TYPE_NAME: $name_value; mysql_native_type: $mysql_value\n";
}
$dbh->do("CREATE TABLE column_info (f1 date)");
my $sth = $dbh->column_info(undef, undef, 'column_info', '%');
while (my $hash_ref = $sth->fetchrow_hashref()) {
print "DATA_TYPE: $hash_ref->{DATA_TYPE}; TYPE_NAME: $hash_ref->{TYPE_NAME}\n";
}
The last line of this script will print "91", making a match impossible with the data from type_info_all(). The correct value should be 9.
Suggested fix:
diff -r -u DBD-mysql-3.0008_1/lib/DBD/mysql.pm dbd-mysql-mine-datetime/lib/DBD/mysql.pm
--- DBD-mysql-3.0008_1/lib/DBD/mysql.pm 2006-10-16 16:11:04.000000000 +0300
+++ dbd-mysql-mine-datetime/lib/DBD/mysql.pm 2006-11-05 15:49:27.000000000 +0200
@@ -397,11 +397,11 @@
}
elsif ($basetype =~ /date|time/) { # date/datetime/time/timestamp
if ($basetype eq 'time' or $basetype eq 'date') {
- $info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TYPE_TIME() : SQL_TYPE_DATE();
+ $info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TIME() : SQL_DATE();
$info->{COLUMN_SIZE} = ($basetype eq 'time') ? 8 : 10;
}
else { # datetime/timestamp
- $info->{DATA_TYPE} = SQL_TYPE_TIMESTAMP();
+ $info->{DATA_TYPE} = SQL_TIMESTAMP();
$info->{SQL_DATA_TYPE} = SQL_DATETIME();
$info->{SQL_DATETIME_SUB} = $info->{DATA_TYPE} - ($info->{SQL_DATA_TYPE} * 10);
$info->{COLUMN_SIZE} = ($basetype eq 'datetime') ? 19 : $type_params[0] || 14;
Description: Hello, When you call column_info() on a table that contains date, time or timestamp columns, a wrong value is returned in the DATA_TYPE column. The value returned for date columns is SQL_TYPE_DATE which is not used anywhere else in the code (and in DBI's source). type_info_all() returns SQL_DATE for the same column type, which makes matching the data from type_info_all() and column_info() impossible for those column types. How to repeat: my $dbh = DBI->connect('DBI:mysql:database=test'); my @type_info = @{$dbh->type_info_all()}; my $sql_col = $type_info[0]->{DATA_TYPE}; my $name_col = $type_info[0]->{TYPE_NAME}; my $mysql_col = $type_info[0]->{mysql_native_type}; foreach my $type (@type_info[1..$#type_info]) { my $sql_value = $type->[$sql_col]; my $mysql_value = $type->[$mysql_col]; my $name_value = $type->[$name_col]; print "DATA_TYPE: $sql_value; TYPE_NAME: $name_value; mysql_native_type: $mysql_value\n"; } $dbh->do("CREATE TABLE column_info (f1 date)"); my $sth = $dbh->column_info(undef, undef, 'column_info', '%'); while (my $hash_ref = $sth->fetchrow_hashref()) { print "DATA_TYPE: $hash_ref->{DATA_TYPE}; TYPE_NAME: $hash_ref->{TYPE_NAME}\n"; } The last line of this script will print "91", making a match impossible with the data from type_info_all(). The correct value should be 9. Suggested fix: diff -r -u DBD-mysql-3.0008_1/lib/DBD/mysql.pm dbd-mysql-mine-datetime/lib/DBD/mysql.pm --- DBD-mysql-3.0008_1/lib/DBD/mysql.pm 2006-10-16 16:11:04.000000000 +0300 +++ dbd-mysql-mine-datetime/lib/DBD/mysql.pm 2006-11-05 15:49:27.000000000 +0200 @@ -397,11 +397,11 @@ } elsif ($basetype =~ /date|time/) { # date/datetime/time/timestamp if ($basetype eq 'time' or $basetype eq 'date') { - $info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TYPE_TIME() : SQL_TYPE_DATE(); + $info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TIME() : SQL_DATE(); $info->{COLUMN_SIZE} = ($basetype eq 'time') ? 8 : 10; } else { # datetime/timestamp - $info->{DATA_TYPE} = SQL_TYPE_TIMESTAMP(); + $info->{DATA_TYPE} = SQL_TIMESTAMP(); $info->{SQL_DATA_TYPE} = SQL_DATETIME(); $info->{SQL_DATETIME_SUB} = $info->{DATA_TYPE} - ($info->{SQL_DATA_TYPE} * 10); $info->{COLUMN_SIZE} = ($basetype eq 'datetime') ? 19 : $type_params[0] || 14;