Bug #69974 [Com]: PDO MySQL with PDO::MYSQL_ATTR_DIRECT_QUERY fetching MySQL DECIMAL as string
Edit report at https://bugs.php.net/bug.php?id=69974&edit=1
ID: 69974
Comment by: ryan dot jentzsch at gmail dot com
Reported by: os at irj dot ru
Summary: PDO MySQL with PDO::MYSQL_ATTR_DIRECT_QUERY fetching
MySQL DECIMAL as string
Status: Open
Type: Bug
Package: PDO MySQL
Operating System: Debian Sid 64
PHP Version: 7.0.0alpha2
Block user comment: N
Private report: N
New Comment:
Agreed that DECIMAL/DOUBLE/NUMERIC can be very large numbers. However, a better solution than
returning a string is to check for an overflow, and if there is one then cast the value as a string,
otherwise return the actual value as the type as it exists in the database.
Do this until "type affinity" RFC can be voted on and implemented.
If I can free up some time I will do a pull request and work on this myself.
Previous Comments:
------------------------------------------------------------------------
[2015-07-01 12:18:22] yohgaki@php.net
Better approach for type conversion is "type affinity". Get all data as
"string", then apply affinity.
https://wiki.php.net/rfc/introduce-type-affinity
------------------------------------------------------------------------
[2015-07-01 12:09:48] yohgaki@php.net
DECIMAL/NUMERIC could be huge number. It cannot fit into PHP's native types. Therefore, it
should be "string".
BTW, AFAIK, MySQL supports unsigned 64 bit int. PHP's "int" is signed int and
it's either 32 bit or 64 bit. It can overflow. I'm not sure how current implementation
handles this. Return as "string" also? It should. IMO. Returning broken data from database
is simply evil.
------------------------------------------------------------------------
[2015-07-01 05:52:31] os at irj dot ru
Description:
------------
PHP 7 PDO MySQL with PDO::MYSQL_ATTR_DIRECT_QUERY fetching MySQL DECIMAL type as string type, but
expected any type of number type (etc. double).
Tested at Debian sid X64 with latest packages
Test script:
---------------
<?php
declare(strict_types=1);
/*
create database php7 default charset 'utf8';
use php7;
create table types (
id int unsigned not null primary key,
signed_int int signed,
unsigned_int int unsigned,
signed_float float signed,
unsigned_float float unsigned,
signed_decimal decimal(10,2) signed,
unsigned_decimal decimal(10,2) unsigned
);
insert into
types
(id, signed_int, unsigned_int, signed_float,
unsigned_float, signed_decimal, unsigned_decimal)
values
('1', '-30', '44', '-300.33', '334.00033',
'-324.34', '64.23')
;
*/
$dbh = new PDO(
"mysql:host=localhost;dbname=php7;unix_socket=/var/run/mysqld/mysqld.sock",
"root" );
$dbh->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, false);
$sth = $dbh->prepare("select * from types");
$sth->execute();
$row = $sth->fetchObject("stdClass");
print "Type of id:" . gettype( $row->id ) . "\n";
print "Type of signed_int:" . gettype( $row->signed_int ) .
"\n";
print "Type of unsigned_int:" . gettype( $row->unsigned_int ) .
"\n";
print "Type of signed_float:" . gettype( $row->signed_float ) .
"\n";
print "Type of unsigned_float:" . gettype( $row->unsigned_float ) .
"\n";
print "Type of signed_decimal:" . gettype( $row->signed_decimal ) .
"\n";
print "Type of unsigned_decimal:" . gettype( $row->unsigned_decimal ) .
"\n";
Expected result:
----------------
Type of id:integer
Type of signed_int:integer
Type of unsigned_int:integer
Type of signed_float:double
Type of unsigned_float:double
Type of signed_decimal:double
Type of unsigned_decimal:double
Actual result:
--------------
Type of id:integer
Type of signed_int:integer
Type of unsigned_int:integer
Type of signed_float:double
Type of unsigned_float:double
Type of signed_decimal:string
Type of unsigned_decimal:string
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=69974&edit=1
Thread (5 messages)