Bug #9612 Column type DOUBLE fails to store input correctly, but TEXT does...
Submitted: 4 Apr 2005 14:16 Modified: 7 Apr 2005 1:30
Reporter: Bogus Exception Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:v4.0.21 OS:Linux (Linux tcas02 2.4.21-226-smp #1 S)
Assigned to: Jon Stephens CPU Architecture:Any

[4 Apr 2005 14:16] Bogus Exception
Description:
(see: http://groups-beta.google.com/group/comp.lang.perl.modules/browse_thread/thread/1459c498b9...)

I'm not sure if 'serious' is an appropriate prioritization, but we're unable to use column types DOUBLE as a result. You decide. Also, your "Categories" above don't include core functionality bugs liek this one, so forgive me if 'Co

Given:

pat@tcas02:~> mysql -V
mysql  Ver 12.22 Distrib 4.0.21, for pc-linux (i686)

cpan> autobundle
Package namespace         installed    latest  in CPAN file
DBD::mysql                 2.9005_3    2.9006  R/RU/RUDY/DBD-mysql-2.9006.tar.gz
DBD::mysql::GetInfo           undef     undef  R/RU/RUDY/DBD-mysql-2.9006.tar.gz
DBI::DBD                      11.21     11.21  T/TI/TIMB/DBI-1.48.tar.gz

pat@tcas02:~> perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration:
  Platform:
    osname=linux, osvers=2.4.19, archname=i586-linux-thread-multi
    uname='linux brooks 2.4.19 #1 thu nov 14 12:14:04 utc 2002 i686 unknown '
    config_args='-ds -e -Dprefix=/usr -Dusethreads -Di_db -Di_dbm -Di_ndbm -Di_gdbm -Duseshrplib=true'
    hint=recommended, useposix=true, d_sigaction=define
    usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
    useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
    use64bitint=undef use64bitall=undef uselongdouble=undef
    usemymalloc=n, bincompat5005=undef
[...]
Characteristics of this binary (from libperl): 
  Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES PERL_IMPLICIT_CONTEXT
  Built under linux
  Compiled at Apr  2 2003 21:16:30
  @INC:
    /usr/lib/perl5/5.8.0/i586-linux-thread-multi
    /usr/lib/perl5/5.8.0
    /usr/lib/perl5/site_perl/5.8.0/i586-linux-thread-multi
    /usr/lib/perl5/site_perl/5.8.0
    /usr/lib/perl5/site_perl
    .

How to repeat:
Newsgroups: comp.lang.perl.modules 
From: "pat" <pat.trai...@gmail.com> - Find messages by this author  
Date: 3 Apr 2005 18:39:21 -0700 
Local: Sun, Apr 3 2005 6:39 pm  
Subject: Re: dupe times in Time::HiRes - problem in DBI or DBD::MySQL or MySQL or the SQL Standard. 

Jonathan, 

I'm still not sure I'm describing the problem correctly, as we're not 
talking about stored procedures. In fact, we're talking about the 
simplest kind of insert possible. The column types are what will 
break/fix the database table. In the example below, column type 'TEXT' 
works (assumes that TINYTEXT would as well..), and column type DOUBLE 
does not. Allow me to illustrate how bad it is with only 10 rows 
inserted: 

We start with 2 empty tables with the following schemas: 

(Forive any wrapping-it isn't my fault!) 

mysql> desc rawText;desc rawDouble; 
+-------------------+---------­---------+------+-----+-------­--+----------------+ 
| Field             | Type             | Null | Key | Default | Extra 
       | 
+-------------------+---------­---------+------+-----+-------­--+----------------+ 
| id                | int(11) unsigned |      | PRI | NULL    | 
auto_increment | 
| timeDateInserted  | timestamp(14)    | YES  |     | NULL    | 
       | 
| timeDateInsertedP | text             | YES  |     | NULL    | 
       | 
+-------------------+---------­---------+------+-----+-------­--+----------------+ 
3 rows in set (0.00 sec) 

+-------------------+---------­---------+------+-----+-------­--+----------------+ 
| Field             | Type             | Null | Key | Default | Extra 
       | 
+-------------------+---------­---------+------+-----+-------­--+----------------+ 
| id                | int(11) unsigned |      | PRI | NULL    | 
auto_increment | 
| timeDateInserted  | timestamp(14)    | YES  |     | NULL    | 
       | 
| timeDateInsertedP | double(21,6)     | YES  |     | NULL    | 
       | 
+-------------------+---------­---------+------+-----+-------­--+----------------+ 
3 rows in set (0.00 sec) 

Now for our test script. (Purists: I could have done this in less 
lines, but I'm not in a contest. This is just to allow you to reproduce 
the error yourself.) 

#!/usr/bin/perl 
use strict; 
use DBI; 
use Time::HiRes qw(gettimeofday); 
use Time::Format qw(%time %strftime %manip); 
my $dbName      = "TimeTest"; 
my $dbUser      = ''; 
my $dbPass      = ''; 
my $tableName   = "rawText"; 
#my $tableName   = "rawDouble"; 
$| = 1; 
my $dbh = connectDB($dbName,$dbUser,$dbP­ass); 
$dbh->{AutoCommit} = 1; 
my $prepareSchema = "INSERT INTO $tableName 
(timeDateInserted,timeDateInse­rtedP) VALUES (NULL,?)"; 
my $sth = $dbh->prepare(qq{$prepareSchem­a}); 
my $count = 1; 
while ($count < 11){ 
        undef $ptime; # paranoid... 
        $ptime = getSuperFineTime(); 
        print "This is my row: [$count] and this is the ptime: 
[$ptime]\n"; 
        $sth->execute($ptime); 
        $count++; 
} 
disconnect($dbh); 
sub getSuperFineTime { 
my $ptime = $time{'yyyymmddhhmmss.uuuuuu'}­; 
return $ptime; 
} 

When inserting into rawText, STDOUT shows: 

This is my count: [1] and this is the time: [20050403213102.343628] 
This is my count: [2] and this is the time: [20050403213102.351197] 
This is my count: [3] and this is the time: [20050403213102.351660] 
This is my count: [4] and this is the time: [20050403213102.352056] 
This is my count: [5] and this is the time: [20050403213102.352458] 
This is my count: [6] and this is the time: [20050403213102.352855] 
This is my count: [7] and this is the time: [20050403213102.353236] 
This is my count: [8] and this is the time: [20050403213102.353622] 
This is my count: [9] and this is the time: [20050403213102.354007] 
This is my count: [10] and this is the time: [20050403213102.354396] 

Correct. 

Database: 

mysql> SELECT * FROM rawText; 
+----+------------------+-----­------------------+ 
| id | timeDateInserted | timeDateInsertedP     | 
+----+------------------+-----­------------------+ 
|  1 |   20050403213102 | 20050403213102.343628 | 
|  2 |   20050403213102 | 20050403213102.351197 | 
|  3 |   20050403213102 | 20050403213102.351660 | 
|  4 |   20050403213102 | 20050403213102.352056 | 
|  5 |   20050403213102 | 20050403213102.352458 | 
|  6 |   20050403213102 | 20050403213102.352855 | 
|  7 |   20050403213102 | 20050403213102.353236 | 
|  8 |   20050403213102 | 20050403213102.353622 | 
|  9 |   20050403213102 | 20050403213102.354007 | 
| 10 |   20050403213102 | 20050403213102.354396 | 
+----+------------------+-----­------------------+ 
10 rows in set (0.00 sec) 

Good matches, no problem. Row for row, all went in the way it should 
have, and came out good as well. 

Now, we send the exact same code to rawDouble STDOUT shows: 

This is my count: [1] and this is the time: [20050403213307.705955] 
This is my count: [2] and this is the time: [20050403213307.712696] 
This is my count: [3] and this is the time: [20050403213307.713165] 
This is my count: [4] and this is the time: [20050403213307.713572] 
This is my count: [5] and this is the time: [20050403213307.713969] 
This is my count: [6] and this is the time: [20050403213307.714368] 
This is my count: [7] and this is the time: [20050403213307.714756] 
This is my count: [8] and this is the time: [20050403213307.715150] 
This is my count: [9] and this is the time: [20050403213307.715537] 
This is my count: [10] and this is the time: [20050403213307.715926] 

No biggie, right? Each row, as above, is a unique number. 

Hold on though... Lets look at what was actually stored in the 
database: 

mysql> SELECT * FROM rawDouble; 
+----+------------------+-----­------------------+ 
| id | timeDateInserted | timeDateInsertedP     | 
+----+------------------+-----­------------------+ 
|  1 |   20050403213307 | 20050403213307.707031 | 
|  2 |   20050403213307 | 20050403213307.710938 | 
|  3 |   20050403213307 | 20050403213307.714844 | 
|  4 |   20050403213307 | 20050403213307.714844 | 
|  5 |   20050403213307 | 20050403213307.714844 | 
|  6 |   20050403213307 | 20050403213307.714844 | 
|  7 |   20050403213307 | 20050403213307.714844 | 
|  8 |   20050403213307 | 20050403213307.714844 | 
|  9 |   20050403213307 | 20050403213307.714844 | 
| 10 |   20050403213307 | 20050403213307.714844 | 
+----+------------------+-----­------------------+ 
10 rows in set (0.01 sec) 

Incorrect. 

pat 
:) 

Suggested fix:
Test with at least the sample script supplied.
[4 Apr 2005 14:23] Bogus Exception
NOTE: As you'll no doubt figure out, the script used to demonstrate the 'bug' uses a connect method not supplied with the code. Please feel free to use the connect method of your choosing. -pat :)
[7 Apr 2005 0:35] Hartmut Holzgraefe
looks like our documentation is not clear enough in distinguishing btw. bits and decimal digits 

double has a mantissa of 52bit which is ~15 decimal digits,
YYYYMMDDhhmmss already has 14 digits so you've only
about one decimal left ... exactly what you see in the result
output ...

changing category to documentation
[7 Apr 2005 1:30] Jon Stephens
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated Manual to clarify that precision arg for FLOAT and DOUBLE refers to bits, not decimals. Added note about approximate precision in decimal places.
[7 Apr 2005 6:34] Jon Stephens
Followup: What I said previously concerning DOUBLE is correct; however, the second argument to FLOAT does in fact specify digits, and not bits - my bad. I've fixed this in the Manual.