php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #69974 PDO MySQL with PDO::MYSQL_ATTR_DIRECT_QUERY fetching MySQL DECIMAL as string
Submitted: 2015-07-01 05:52 UTC Modified: 2020-12-11 10:39 UTC
Votes:6
Avg. Score:4.5 ± 0.8
Reproduced:6 of 6 (100.0%)
Same Version:3 (50.0%)
Same OS:3 (50.0%)
From: os at irj dot ru Assigned:
Status: Wont fix Package: PDO MySQL
PHP Version: 7.0.0alpha2 OS: Debian Sid 64
Private report: No CVE-ID: None
 [2015-07-01 05:52 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-07-01 12:09 UTC] 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 12:18 UTC] 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-02 16:25 UTC] ryan dot jentzsch at gmail dot com
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.
 [2020-12-11 10:39 UTC] nikic@php.net
-Status: Open +Status: Wont fix
 [2020-12-11 10:39 UTC] nikic@php.net
It's not just a question of overflow. DECIMAL is a fixed-point type, while float is a floating-point type. float can only store an approximation of the DECIMAL value. And DECIMAL values are used precisely for cases where the distinction is important.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 16:01:28 2024 UTC