Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 93710
Status: rejected
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: Doug.Hershberger [...] algenol.com
Cc: pali [...] cpan.org
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: Error in DBD:mysql retrieving FLOATs when the flag 'mysql_server_prepare=1' is set
Date: Mon, 10 Mar 2014 16:19:43 +0000
To: "bug-DBD-mysql [...] rt.cpan.org" <bug-DBD-mysql [...] rt.cpan.org>
From: Doug Hershberger <Doug.Hershberger [...] algenol.com>
Download (untitled) / with headers
text/plain 6.5k
I have encountered what I believe to be an error in DBD::mysql. This error only seems to occur when the flag 'mysql_server_prepare=1' is set. If this is true, then FLOATs returned from the database will have slightly different values than when the flag is not set. I suppose it would be fairly straightforward to just round off the values of all FLOATs, but since this rather unexpected bug arises from a change which would seem to have no relevance, it is a bit disconcerting. It would be nice to know if this bug is on the roadmap to be fixed any time soon. Thanks again for any help or insight that you can provide. Doug Hershberger Below I give instructions for reproducing this error. All commands and output where generated on computers running Red Hat Enterprise Linux r6.4. This was tested on 64 bit physical boxes as well as computers running as 64 bit virtual machines hosted on a Hyper-V server. This was tested with DBD::mysql v4.013, v4.020 and v4.026; DBI c1.609 and v1.631; mySQL v5.5.17-log and v5.1.71 although not all combinations of those were tried. #Setting up the database for the test case mysql -uroot -p CREATE USER 'chester'@'localhost' IDENTIFIED BY 'myTestPW'; GRANT ALL ON test.* TO 'chester'@'localhost'; use test; CREATE TABLE preparedTest (name VARCHAR(20), floatVar FLOAT, doubVar DOUBLE); INSERT INTO preparedTest (name, floatVar, doubVar) VALUES ("example", .96, .96); Show quoted text
mysql> select * from preparedTest;
#Output which shows the table structure +---------+----------+---------+ | name | floatVar | doubVar | +---------+----------+---------+ | example | 0.96 | 0.96 | +---------+----------+---------+ #A dump of this data is attached and provided below so that you can recreate the database #Put the dump into test-file.sql #load the database into mySQL mysql test -uchester -pmyTestPW < test-file.sql #Perl code to test this error is attached and provided below. #put that code in the file preparedTest.pl perl preparedTest.pl #returns the following results 1..6 DBI::VERSION: 1.609 DBD::mysql::VERSION: 4.026 Running tests with no prepared statements. Name:example ok 1 - Got the right row with no prepared statements. 0.96 ok 2 - Float values work with no prepared statements. 0.96 ok 3 - Double values work with no prepared statements. Running tests with prepared statements. Name:example ok 4 - Got the right row with prepared statements. 0.959999978542328 not ok 5 - Float values work with prepared statements. # Failed test 'Float values work with prepared statements.' # at ./preparedTest.pl line 31. 0.96 ok 6 - Double values work with prepared statements. # Looks like you failed 1 test of 6. ########### #Here is a dump of the data you can put this in a file called test-file.sql: ########### use test; -- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.1.73 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `preparedTest` -- DROP TABLE IF EXISTS `preparedTest`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `preparedTest` ( `name` varchar(20) DEFAULT NULL, `floatVar` float DEFAULT NULL, `doubVar` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `preparedTest` -- LOCK TABLES `preparedTest` WRITE; /*!40000 ALTER TABLE `preparedTest` DISABLE KEYS */; INSERT INTO `preparedTest` VALUES ('example',0.96,0.96); /*!40000 ALTER TABLE `preparedTest` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; ########### #END of SQL data dump test-file.sql ########### ########### #Enter this code into preparedTest.pl ########### #!/usr/bin/perl use strict; use DBI; # load DBI database interaction routines print "DBI::VERSION: $DBI::VERSION\n"; use DBD::mysql; use Test::Simple tests => 6; print "DBD::mysql::VERSION: " . $DBD::mysql::VERSION . "\n\n"; #First without the prepare statement my $dbh=DBI->connect('DBI:mysql:database=test;host=localhost', 'chester', 'myTestPW', {'RaiseError' => 1}) or die "ConnectionEror: $DBI::errstr\n"; testIt($dbh, "no prepared statements"); #Now with the prepare statement $dbh=DBI->connect('DBI:mysql:database=test;host=localhost;mysql_server_prepare=1', 'chester', 'myTestPW', {'RaiseError' => 1}) or die "ConnectionEror: $DBI::errstr\n"; testIt($dbh, "prepared statements"); sub testIt { my $dbh = shift; my $condition = shift; my $sth = $dbh->prepare("SELECT * FROM preparedTest;"); $sth->execute or die "SQL Error: $DBI::errstr\n"; my @rows = $sth->fetchrow_array(); my $name = $rows[0]; print "Running tests with " . $condition . ".\n"; print "Name:"; print $name . "\n"; ok($name eq "example", "Got the right row with " . $condition . "."); my $floater = $rows[1]; print $floater . "\n"; #I'm a bit surprised this comparison works for any case because I thought that you # can't get correct results using == with FLOATs due to internal limitations. But I # guess this is just another thing that Perl automagically does for you ok($floater == .96, "Float values work with " . $condition . "."); my $double = $rows[2]; print $double . "\n"; ok($double == .96, "Double values work with " . $condition . "."); print "\n\n"; $sth->finish(); $dbh->disconnect(); } ########### #END code for preparedTest.pl ########### Thanks -- Doug Hershberger Associate Director, Bioinformatics [cid:663C72F1-22F0-47E4-825A-1E4FB7E35B7C] 16121 Lee Road Ft. Myers, FL 33912 P 239.284.1115 C 650.898.7819 F 239.284.1199 Skype doug.hershberger.algenol
Download (untitled) / with headers
text/html 19.4k

Message body is not shown because it is too large.

F5F56FC5-6EEF-459D-A431-F12F14552D2C.png
Download preparedTest.pl
text/x-perl 1.3k

Message body is not shown because sender requested not to inline it.

Download test-file.sql
application/octet-stream 1.8k

Message body not shown because it is not plain text.

RT-Send-CC: pali [...] cpan.org
@Pali: is this more or less related to the work you contributed earlier this week?
Download (untitled) / with headers
text/plain 1.1k
On Pon Sep 19 15:35:35 2016, MICHIELB wrote: Show quoted text
> @Pali: is this more or less related to the work you contributed > earlier this week?
No. But behaviour written in this ticket is expected. Reason is very simple: You cannot store exact decimal value 0.96 into float or double (according to IEEE 754 with base 2). So rounding errors are expected. More, as you can see in DBD mysql source code, there are two different protocols for communication with mysql server. First one is text orientated where all values are send and retrieved as strings (also floating point numbers) and second is binary (where numbers are in their native format, not ASCII strings). First protocol is used when mysql_server_prepare is not set and second when mysql_server_prepare=1. So rounding and casting differences can be there based on mysql_server_prepare value. But this is related to mysql server and protocol itself, not DBD mysql driver which just implement mysql protocol support. Anyway, comparing two floating point values on equality does not work at all. I would say it is very suspicious that values are really equal when mysql_server_prepare is not used :-)
Download (untitled) / with headers
text/plain 1.3k
On Pon Sep 19 16:07:32 2016, PALI wrote: Show quoted text
> On Pon Sep 19 15:35:35 2016, MICHIELB wrote:
> > @Pali: is this more or less related to the work you contributed > > earlier this week?
> > No. But behaviour written in this ticket is expected. Reason is very > simple: > > You cannot store exact decimal value 0.96 into float or double > (according to IEEE 754 with base 2). So rounding errors are expected. > > More, as you can see in DBD mysql source code, there are two different > protocols for communication with mysql server. First one is text > orientated where all values are send and retrieved as strings (also > floating point numbers) and second is binary (where numbers are in > their native format, not ASCII strings). First protocol is used when > mysql_server_prepare is not set and second when > mysql_server_prepare=1. So rounding and casting differences can be > there based on mysql_server_prepare value. > > But this is related to mysql server and protocol itself, not DBD mysql > driver which just implement mysql protocol support. > > Anyway, comparing two floating point values on equality does not work > at all. I would say it is very suspicious that values are really equal > when mysql_server_prepare is not used :-)
Based on above description I would close this ticket as NOT A BUG, but I do not have rights for that. It is not possible to store value 0.96 as float.
+1 what Pali says!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.