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)



From Doug.Hershberger [...] algenol.com Mon Mar 10 12: 19:54 2014
MIME-Version: 1.0
X-Spam-Status: No, score=-2.343 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, BIGNUM_EMAILS=0.091, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-0.535] autolearn=ham
X-Spam-Flag: NO
X-Virus-Checked: Checked
Content-Language: en-US
Content-Type: multipart/mixed; boundary="_007_CF435E5C5F37Edoughershbergeralgenolcom_"
Message-ID: <CF435E5C.5F37E%doug.hershberger [...] algenol.com>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-MS-Tnef-Correlator:
X-Spam-Score: -2.343
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 41171240547 for <cpan-bug+DBD-mysql [...] hipster.bestpractical.com>; Mon, 10 Mar 2014 12:19:54 -0400 (EDT)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HhUdOiRcGoHX for <cpan-bug+DBD-mysql [...] hipster.bestpractical.com>; Mon, 10 Mar 2014 12:19:52 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 54BE424051C for <bug-DBD-mysql [...] rt.cpan.org>; Mon, 10 Mar 2014 12:19:50 -0400 (EDT)
Received: (qmail 25317 invoked by alias); 10 Mar 2014 16:19:50 -0000
Received: from mail.algenol.com (HELO mail.algenol.com) (74.112.168.226) by la.mx.develooper.com (qpsmtpd/0.28) with ESMTP; Mon, 10 Mar 2014 09:19:47 -0700
Received: from MONOXIDE.algenol.local ([fe80::7190:1f2b:8b91:c308]) by Dioxide.algenol.local ([fe80::85cc:2707:db71:69ed%17]) with mapi id 14.03.0174.001; Mon, 10 Mar 2014 12:19:44 -0400
Delivered-To: cpan-bug+DBD-mysql [...] hipster.bestpractical.com
Subject: Error in DBD:mysql retrieving FLOATs when the flag 'mysql_server_prepare=1' is set
Return-Path: <Doug.Hershberger [...] algenol.com>
Thread-Index: AQHPPHyL0hzb32cUJEC2LvraAC2FSg==
X-RT-Mail-Extension: dbd-mysql
X-Original-To: cpan-bug+DBD-mysql [...] hipster.bestpractical.com
X-Spam-Check-BY: la.mx.develooper.com
Date: Mon, 10 Mar 2014 16:19:43 +0000
X-Spam-Level:
X-MS-Has-Attach: yes
Thread-Topic: Error in DBD:mysql retrieving FLOATs when the flag 'mysql_server_prepare=1' is set
X-Originating-Ip: [192.168.28.84]
Accept-Language: en-US
To: "bug-DBD-mysql [...] rt.cpan.org" <bug-DBD-mysql [...] rt.cpan.org>
From: Doug Hershberger <Doug.Hershberger [...] algenol.com>
X-RT-Interface: Email
Content-Length: 0
Content-Type: multipart/related; boundary="_006_CF435E5C5F37Edoughershbergeralgenolcom_"; type="multipart/alternative"
Content-Length: 0
Content-Type: multipart/alternative; boundary="_000_CF435E5C5F37Edoughershbergeralgenolcom_"
Content-Length: 0
content-type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: ascii
Content-Length: 6660
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
content-type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-ID: <536E367E69B5434694A473A1DE8F1970 [...] algenol.local>
X-RT-Original-Encoding: ascii
Content-Length: 19947
Download (untitled) / with headers
text/html 19.4k

Message body is not shown because it is too large.

Content-Description: F5F56FC5-6EEF-459D-A431-F12F14552D2C.png
Content-Type: image/png; name="F5F56FC5-6EEF-459D-A431-F12F14552D2C.png"
Content-Disposition: inline; creation-date="Mon, 10 Mar 2014 16:19:43 GMT"; filename="F5F56FC5-6EEF-459D-A431-F12F14552D2C.png"; modification-date="Mon, 10 Mar 2014 16:19:43 GMT"; size="8931"
Content-Transfer-Encoding: base64
Content-ID: <663C72F1-22F0-47E4-825A-1E4FB7E35B7C>
Content-Length: 8931
F5F56FC5-6EEF-459D-A431-F12F14552D2C.png
Content-Description: preparedTest.pl
Content-Type: text/x-perl-script; name="preparedTest.pl"
Content-Disposition: attachment; creation-date="Mon, 10 Mar 2014 16:19:43 GMT"; filename="preparedTest.pl"; modification-date="Mon, 10 Mar 2014 16:19:43 GMT"; size="1360"
Content-Transfer-Encoding: base64
Content-ID: <02F1F4E1C0ABE04BBD58992D9FCEAC3F [...] algenol.local>
Content-Length: 1360
Download preparedTest.pl
text/x-perl 1.3k

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

Content-Description: test-file.sql
Content-Type: application/octet-stream; name="test-file.sql"
Content-Disposition: attachment; creation-date="Mon, 10 Mar 2014 16:19:43 GMT"; filename="test-file.sql"; modification-date="Mon, 10 Mar 2014 16:19:43 GMT"; size="1908"
Content-Transfer-Encoding: base64
Content-ID: <C57AB5D3346D3A4DAB8520B04346C678 [...] algenol.local>
Content-Length: 1908
Download test-file.sql
application/octet-stream 1.8k

Message body not shown because it is not plain text.

MIME-Version: 1.0
In-Reply-To: <CF435E5C.5F37E%doug.hershberger [...] algenol.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CF435E5C.5F37E%doug.hershberger [...] algenol.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-27128-1474313735-1947.93710-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
RT-Send-CC: pali [...] cpan.org
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 82
@Pali: is this more or less related to the work you contributed earlier this week?
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-27128-1474313735-1947.93710-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CF435E5C.5F37E%doug.hershberger [...] algenol.com> <rt-4.0.18-27128-1474313735-1947.93710-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-12328-1474315652-208.93710-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 1150
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 :-)
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-12328-1474315652-208.93710-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CF435E5C.5F37E%doug.hershberger [...] algenol.com> <rt-4.0.18-27128-1474313735-1947.93710-0-0 [...] rt.cpan.org> <rt-4.0.18-12328-1474315652-208.93710-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-5639-1479587401-28.93710-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 1393
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.
MIME-Version: 1.0
In-Reply-To: <CF435E5C.5F37E%doug.hershberger [...] algenol.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CF435E5C.5F37E%doug.hershberger [...] algenol.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-11060-1479633689-963.93710-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 18
+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.