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: 2015-07-01 12:18 UTC
Votes:3
Avg. Score:4.7 ± 0.5
Reproduced:3 of 3 (100.0%)
Same Version:1 (33.3%)
Same OS:3 (100.0%)
From: os at irj dot ru Assigned:
Status: Open Package: PDO MySQL
PHP Version: 7.0.0alpha2 OS: Debian Sid 64
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: os at irj dot ru
New email:
PHP Version: OS:

 

 [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

Add a Patch

Pull Requests

Add a Pull Request

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.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Thu Dec 12 18:01:24 2019 UTC