DECIMAL col type bug in DBD::mysql w/mysql 5

I believe I have discovered a bug (I'm guessing it's in DBD::mysql).
When connecting to mysql 5, it returns the type of a decimal column
as SQL_VARCHAR, as opposed to SQL_DECIMAL returned when the mysql
server is version 4.1.

The details of my setup:

Mac OS X Tiger 10.4.3
perl 5.8.6
DBI 1.47
DBD::mysql 3.0002 (and 3.0002_04) (built with mysql-5.0.16 libraries)
mysql 5.0.16
mysql 4.1.15

The script below demonstrates the bug, outputing ...

value 12 SQL_VARCHAR

.... when connecting to a mysql 5.0.16 server on localhost, vs ...

value 3 SQL_DECIMAL

.... when connecting to a mysql 4.1.15 server on localhost.

Ray Zimmerman
Director, Laboratory for Experimental Economics and Decision Research
428-B Phillips Hall, Cornell University, Ithaca, NY 14853
phone: (607) 255-9645

-------------------------------------

#!/usr/bin/perl -w
use DBI;

## get DBI type map
my %map;
foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
$map{&{"DBI::$_"}} = $_;
}

my $dbh = DBI->connect('DBI:mysql:test', 'test');
my $table = 'mysql5bug';
my $drop = "DROP TABLE IF EXISTS $table";
my $create = "CREATE TABLE $table (value decimal(5,2));";
my $select = "SELECT * FROM $table WHERE 1 = 0";

## create table and get column types
$dbh->do($drop) or die $dbh->errstr;
$dbh->do($create) or die $dbh->errstr;
my $sth = $dbh->prepare( $select );
my $rv = $sth->execute;
my $fields = $sth->{NAME};
my $types = $sth->{TYPE};

## print out column types
foreach (0..$#$fields) {
printf("%8s %3d %s\n", $fields->[$_], $types->[$_], $map{$types->
[$_]});
}

## cleanup
$dbh->do($drop) or die $dbh->errstr;
$sth->finish;
$dbh->disconnect;

1;


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DECIMAL col type bug in DBD::mysql w/mysql 5

> -----Ursprüngliche Nachricht-----
> Von: Ray Zimmerman [mailto:rz10@cornell.edu]
> Gesendet: Donnerstag, 1. Dezember 2005 20:22
> I believe I have discovered a bug (I'm guessing it's in DBD::mysql). =

> When connecting to mysql 5, it returns the type of a decimal column =20
> as SQL_VARCHAR, as opposed to SQL_DECIMAL returned when the mysql =20
> server is version 4.1.
I don't think it's DBD::mysql. http://bugs.mysql.com/bug.php?id=3D14923 =
has an
entry from the MySQL people that may be an explanation. Base line: =
"There is
no standard C type for fixed point numeric type, so MySQL server
returns decimal as a string." This comes also up in MySQL QueryBrowser, =
so
it's not just DBD::mysql.

As a workaround, the entry suggests to cast the decimal to some other =
type
(like double). Which may, of course, lead to loss of accuracy.

\Gisbert

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: DECIMAL col type bug in DBD::mysql w/mysql 5

On Dec 2, 2005, at 11:37 AM, Selke, Gisbert W. wrote:
> I don't think it's DBD::mysql. http://bugs.mysql.com/bug.php?
> id=14923 has an
> entry from the MySQL people that may be an explanation. Base line:
> "There is
> no standard C type for fixed point numeric type, so MySQL server
> returns decimal as a string." This comes also up in MySQL
> QueryBrowser, so
> it's not just DBD::mysql.

While this may be related, I think it is a different issue. I'm not
sure where the bug is (server or DBD::mysql), but apparently the
server knows that the column is a DECIMAL type as opposed to VARCHAR,
since SHOW COLUMNS FROM