Bug #113683 for DBD-Pg: Invalid handling numbers

Mon Apr 11 11:37:43 2016 AVKHOZOV [...] cpan.org - Ticket created

Subject:

Invalid handling numbers

Following code: my $sth = $dbh->prepare('select 1.13::numeric as n, 1.13::int, 1.13::float8, 1.13::real as r, 1.13::decimal as d'); $sth->execute; my $row = $sth->fetchrow_hashref; warn Dumper $row; output: $VAR1 = { 'float8' => '1.13', 'int4' => 1, 'r' => '1.13', 'n' => '1.13', 'd' => '1.13' }; All float values treats as string in perl scalar instead of numbers.

Tue Mar 28 22:17:29 2017 greg [...] turnstep.com - Correspondence added

Show quoted text

> All float values treats as string in perl scalar instead of numbers.

The problem is that we cannot easily tell how well a Postgres number will fit into Perl's number. In other words, we can assume that nvtype is a double, but what happens when we try to fit a really large number inside of it? We would need Math::Bigint for some things. See: https://www.postgresql.org/docs/current/static/datatype-numeric.html (see the Range column of that first table in particular) We cannot simply assign 'some' of the numbers as Perl numbers, and the rest as strings, as that would cause all sorts of bugs, so we default to treating everything as a string unless we know it will fit (e.g. integers). Suppose this should go in a FAQ somewhere...

Tue Mar 28 22:17:30 2017 The RT System itself - Status changed from 'new' to 'open'

Wed Mar 29 03:17:24 2017 AVKHOZOV [...] cpan.org - Correspondence added

Thanks for the answer! I think, that all integer (smallint, integer, bigint, smallserial, serial, bigserial) should translated into simple IV/UV in Perl. This only int2, int4 and int8 types. Seems, that many platforms which run perl supports this types. Types 'real' and 'double precision' this is float and double. Also supported by many systems. NV type for scalars. Types decimal and numeric has variable sizes, so I think they need use a string (PV) type in Perl. So, for one type in Postgres, DBD::Pg would have also only one type. On Tue Mar 28 22:17:29 2017, TURNSTEP wrote: Show quoted text

> > All float values treats as string in perl scalar instead of numbers.

> > The problem is that we cannot easily tell how well a Postgres number > will fit into Perl's number. In other words, we can assume that nvtype > is a double, but what happens when we try to fit a really large number > inside of it? We would need Math::Bigint for some things. See: > > https://www.postgresql.org/docs/current/static/datatype-numeric.html > > (see the Range column of that first table in particular) > > We cannot simply assign 'some' of the numbers as Perl numbers, and the > rest as strings, as that would cause all sorts of bugs, so we default > to treating everything as a string unless we know it will fit (e.g. > integers). > > Suppose this should go in a FAQ somewhere...

Wed Mar 29 15:04:30 2017 greg [...] turnstep.com - Correspondence added

I could be on board for some small careful casting changes. Show quoted text

> I think, that all integer (smallint, integer, bigint, smallserial, > serial, bigserial) should translated into simple IV/UV in Perl. This > only int2, int4 and int8 types. Seems, that many platforms which run > perl supports this types.

These already all map to IV, which can easily hold them all (no need to pull in UV). Show quoted text

> Types 'real' and 'double precision' this is float and double. Also > supported by many systems. NV type for scalars.

Yes, I think we can put all the smaller bounded ones in like this, from some quick testing: sv_setnv(sv, strod(value, NULL)); Show quoted text

> Types decimal and numeric has variable sizes, so I think they need use > a string (PV) type in Perl.

Yes. Would be nice to do a complete audit of all the types while we are breaking things, so I'd love to have other people weigh in. Right now, the only types we consider "special" are arrays, int2/4/8, and booleans.

Thu Mar 30 20:08:33 2017 greg [...] turnstep.com - Correspondence added

Show quoted text

> > Types 'real' and 'double precision' this is float and double. Also > > supported by many systems. NV type for scalars.

Applied SvNV things in 535565672dd4070215ec995ff838524c2bd8f391 if you would like to give it a try.

Thu Mar 30 20:08:39 2017 greg [...] turnstep.com - Status changed from 'open' to 'patched'

Fri Mar 31 03:21:48 2017 AVKHOZOV [...] cpan.org - Correspondence added

On Thu Mar 30 20:08:33 2017, TURNSTEP wrote: Show quoted text

> > > Types 'real' and 'double precision' this is float and double. Also > > > supported by many systems. NV type for scalars.

> > Applied SvNV things in 535565672dd4070215ec995ff838524c2bd8f391 if you > would like to give it a try.

I have tested version 39299909de24d8c74db0df1160bb18137ff041de from github. Looks fine, thanks! $ perl pg.pl 3.6.0 SV = NV(0x559a03c00630) at 0x559a03c00648 REFCNT = 1 FLAGS = (NOK,pNOK) NV = 0.1234 $ perl pg.pl 3.5.3 SV = PV(0x56252b7c5240) at 0x56252b718648 REFCNT = 1 FLAGS = (POK,IsCOW,pPOK,UTF8) PV = 0x56252ba6fda0 "0.1234"\0 [UTF8 "0.1234"] CUR = 6 LEN = 10 COW_REFCNT = 1 $ P.S. In 535565672dd4070215ec995ff838524c2bd8f391 in switch started on 3731 line cases mix spaces and tabs.

Tue Apr 18 07:03:09 2017 greg [...] turnstep.com - Correspondence added

Fixed in 3.6.0

Tue Apr 18 07:03:23 2017 greg [...] turnstep.com - Status changed from 'patched' to 'resolved'

Tue Apr 18 07:03:28 2017 greg [...] turnstep.com - Fixed in 3.6.0 added