Bug #128993 for DBD-Pg: DBD::Pg 3.7.0 Wrong floating point numbers

Mon Apr 01 10:48:04 2019 ubhofmann [...] googlemail.com - Ticket created

Subject:

DBD::Pg 3.7.0 Wrong floating point numbers

Output of floating point numbers has changed between DBD::Pg 3.5.3 and 3.7.0. Issues: 1) -0 is returned as +0 2) DBD::Pg lost the ability to return large numbers in scientific notation. Consider a select to a real field containing the number 1.23456789e+12::real. Psql and DBD::Pg 3.5.3 return this number as 1.23457e+12, which is fine. DBD::Pg 3.7.0 returns this number as 1234567950000, which is wrong!

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Fixed in 3.7.0 deleted

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Fixed in 3.7.1 deleted

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Fixed in 3.7.3 deleted

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Fixed in 3.7.4 deleted

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Broken in 3.7.0 added

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Broken in 3.7.1 added

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Broken in 3.7.3 added

Mon Apr 01 10:48:49 2019 ubhofmann [...] googlemail.com - Broken in 3.7.4 added

Mon Apr 01 10:56:08 2019 ubhofmann [...] googlemail.com - Correspondence added

How to repeat: 1) Create test data create table float_test ( f real ); -- hack: insert -0 insert into float_test ( f ) values ( 0 ); update float_test set f = -1.0 * f; -- more test data insert into float_test ( f ) values ( 0 ), ( 16777212 ), ( 1.23456789e+12 ), ( 3.14159265358979323 ); 2) Read data using psql Show quoted text

> select * from float_test;

f ------------- -0 0 1.67772e+07 1.23457e+12 3.14159 Show quoted text

> set extra_float_digits=3;

Show quoted text

> select * from float_test;

f ---------------- -0 0 16777212 1.23456795e+12 3.14159274 ===> All values as expected

Mon Apr 01 11:01:26 2019 ubhofmann [...] googlemail.com - Correspondence added

3) Read data in Perl using DBD::Pg 3.5.3 (test program: see attachment) Data with extra_float_digits = <default> -0 0 1.67772e+07 1.23457e+12 3.14159 Data with extra_float_digits = 3 -0 0 16777212 1.23456795e+12 3.14159274 ===> All values as expected 4) Read data in Perl using DBD::Pg 3.7.0 (test program: see attachment) Data with extra_float_digits = <default> 0 <== Wrong, should be -0 0 16777200 <== Wrong 1234570000000 <== Wrong 3.14159 Data with extra_float_digits = 3 0 <== Wrong, should be -0 0 16777212 1234567950000 <== Wrong 3.14159274

Mon Apr 01 11:02:25 2019 ubhofmann [...] googlemail.com - Correspondence added

Subject:

test_floating_point_numbers.pl

#!/usr/bin/perl use 5.20.1; use utf8; use strict; use warnings; use DBI; my $dbh = DBI->connect( "dbi:Pg:dbname=<dbname>;host=127.0.0.1;port=5432", "<username>", "<password>" ); # ------------------------------------------------------- printf( "Data with extra_float_digits = <default>\n" ); my $th = $dbh->prepare( 'SELECT f from float_test' ); my $retval = $th->execute( @_ ); while ( 1 ) { my @row = $th->fetchrow_array(); last if ( $#row < 0 ); printf( "%s\n", $row[0] ); } $th->finish(); # ------------------------------------------------------- printf( "Data with extra_float_digits = 3\n" ); $dbh->do( 'set extra_float_digits=3' ); $th = $dbh->prepare( 'SELECT f from float_test' ); $retval = $th->execute( @_ ); while ( 1 ) { my @row = $th->fetchrow_array(); last if ( $#row < 0 ); printf( "%s\n", $row[0] ); } $th->finish(); # ------------------------------------------------------- $dbh->disconnect(); __END__

Mon Apr 01 16:48:58 2019 ubhofmann [...] gmail.com - Broken in 3.6.0 added

Mon Apr 01 16:48:58 2019 ubhofmann [...] gmail.com - Broken in 3.6.1 added

Mon Apr 01 16:48:58 2019 ubhofmann [...] gmail.com - Broken in 3.6.2 added

Mon Apr 01 16:53:52 2019 ubhofmann [...] gmail.com - Correspondence added

3.5.3 is the last version without the problem 3.6.0 is the first version with the problem Tested on Linux with PostgreSQL 9.6 and 11, Perl 5.24 and 5.26.

Mon Apr 01 16:53:53 2019 The RT System itself - Status changed from 'new' to 'open'

Sun May 10 02:38:26 2020 twata_1 [...] yahoo.co.jp - Correspondence added

Hello, It seems to me that DBD::Pg 3.12.0 has these issues. Tested on Windows 7 (32bit) with PostgreSQL 10.12, Strawberry Perl 5.26.1. -------------------------------------------------------------------------------- C:\home\bath1>set LANG=C C:\home\bath1>psql -U pguser pgtest10 Password for user pguser: psql (10.12) Type "help" for help. pgtest10=# select * from float_test; f -------------- -0 0 1.67772e+007 1.23457e+012 3.14159 (5 rows) pgtest10=# set extra_float_digits=3; SET pgtest10=# select * from float_test; f ----------------- -0 0 16777212 1.23456795e+012 3.14159274 (5 rows) pgtest10=# -------------------------------------------------------------------------------- C:\home\bath1>module-version DBD::Pg The version of DBD::Pg in C:\home\bath1\perl5\lib\perl5/MSWin32-x86-multi-thread-64int is 3.12.0 C:\home\bath1>perl test_floating_point_numbers.pl Data with extra_float_digits = <default> 0 0 16777200 1234570000000 3.14159 Data with extra_float_digits = 3 0 0 16777212 1234567950000 3.14159274 C:\home\bath1> -------------------------------------------------------------------------------- Thank you, -- twata On 2019-4月-01 月 16:53:52, ubhofmann@gmail.com wrote: Show quoted text

> 3.5.3 is the last version without the problem > > 3.6.0 is the first version with the problem > > Tested on Linux with PostgreSQL 9.6 and 11, Perl 5.24 and 5.26. > >

Mon May 11 14:21:52 2020 greg [...] turnstep.com - Correspondence added

Please see https://rt.cpan.org/Public/Bug/Display.html?id=113683 it may be related.

Tue Jul 14 10:56:45 2020 greg [...] turnstep.com - Correspondence added

Marking as stalled for now until OP replies. This is most likely the purposeful change in version 3.6.0

Tue Jul 14 10:56:46 2020 greg [...] turnstep.com - Status changed from 'open' to 'stalled'

Tue Jul 21 21:15:42 2020 ubhofmann [...] gmail.com - Status changed from 'stalled' to 'open'

Tue Jul 21 21:15:42 2020 ubhofmann [...] gmail.com - Broken in 3.13.0 added

Tue Jul 21 21:26:51 2020 ubhofmann [...] gmail.com - Correspondence added

I was finally able to find the code that introduced the issue. It was a change between DBD-Pg 3.5.3 and 3.6.0. The culprit are the lines below which were added to dbdimp.c, lines 3750+ + case PG_FLOAT4: + case PG_FLOAT8: + sv_setnv(sv, strtod((char *)value, NULL)); + break; If I remove those lines in DBD-Pg 3.6.0, my test program test_floating_point_numbers.pl (see above) prints floating point numbers correctly. Removing those 4 lines from dbdimp.c of DBD-Pg 3.13.0 also solves the issue for DBD-Pg 3.13.0. Disclaimer: I have no idea why those lines were added to DBD-Pg 3.6.0 and what exactly happens if I remove those lines. All I can say is that removing those lines fixes the issue I described in this ticket.

Fri Jul 24 17:02:15 2020 greg [...] turnstep.com - Correspondence added

Show quoted text

> Disclaimer: I have no idea why those lines were added to DBD-Pg 3.6.0 > and what exactly happens if I remove those lines. All I can say is > that removing those lines fixes the issue I described in this ticket.

Those lines are so that numbers coming from the database are cast to Perl's version of "numbers" rather than just strings. Perl will still show exponential format if the number is big enough: try using a test number of 1.23456789e+22 in your test and see what happens.

Fri Jul 24 20:55:31 2020 ubhofmann [...] gmail.com - Correspondence added

Thanks for taking a look into this! I think I understand now. Prior to DBD::Pg 3.6.0, DBD::Pg stored floating-point values as strings. For that reason, printf() printed them exactly as they were returned from PostgreSQL. Starting with DBD::Pg 3.6.0, DBD::Pg stores floating-point values as floating-point numbers. A subsequent printf() in a Perl program may print the numbers in a format which is different to PostgreSQL's output. I think that's reasonable. I can live with that. Please close the ticket. I apologize for the noise. PS: it's easy to bypass DBD::Pg's conversion to floating-point numbers. Just add "::text" after columns of data type 'real' or 'double'. SELECT f::text FROM table

Fri Jul 24 20:56:59 2020 ubhofmann [...] gmail.com - Broken in 3.7.0 deleted

Fri Jul 24 20:56:59 2020 ubhofmann [...] gmail.com - Broken in 3.7.1 deleted

Fri Jul 24 20:56:59 2020 ubhofmann [...] gmail.com - Broken in 3.7.3 deleted

Fri Jul 24 20:56:59 2020 ubhofmann [...] gmail.com - Broken in 3.7.4 deleted

Fri Jul 24 20:56:59 2020 ubhofmann [...] gmail.com - Broken in 3.6.1 deleted

Fri Jul 24 20:56:59 2020 ubhofmann [...] gmail.com - Broken in 3.6.2 deleted

Sat Jul 25 07:14:17 2020 greg [...] turnstep.com - Correspondence added

Show quoted text

> I think that's reasonable. I can live with that. Please close the > ticket. I apologize for the noise.

No worries, thanks for sticking to this. Bugs should get resolved, one way or another. :)

Sat Jul 25 07:14:18 2020 greg [...] turnstep.com - Status changed from 'open' to 'resolved'