php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #81397 PDO_sqlite: DECIMAL values from results are cast to float
Submitted: 2021-08-29 11:11 UTC Modified: 2021-09-01 11:06 UTC
From: me at derrabus dot de Assigned:
Status: Open Package: PDO SQLite
PHP Version: 8.1.0beta3 OS: macOS 11.5
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: me at derrabus dot de
New email:
PHP Version: OS:

 

 [2021-08-29 11:11 UTC] me at derrabus dot de
Description:
------------
In PHP 8.1, the mapping of database types to PHP's native types has been greatly improved. For instance, it has been made sure that an INT value from an SQL result is translated to a PHP integer value where it has been a string previously.

This is of course good news. However, I fear that for DECIMAL values on SQLite, this has been taken a bit too far. DECIMAL is often used in SQL databases for values where an exact representation is important and floating point arithmetic could sneak in rounding errors, for example for currency values. When querying a DECIMAL field from SQLite, I receive a float value instead of a string. I believe that this behavior is unexpected and potentially harmful.

I have attached a script that demonstrates the behavior of the different PDO drivers and posted the output of PHP 8.0 under "expected result". While I consider the changes for INT and FLOAT on MySQL and SQLite to be an improvement, I believe that DECIMAL values should remain strings.

Related issue for Doctrine ORM: https://github.com/doctrine/orm/issues/8963

Test script:
---------------
<?php

function provideDSNs(): Traversable
{
    yield 'sqlite'   => new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    yield 'mysql'    => new PDO('mysql:host=127.0.0.1;dbname=test', 'testuser', 'testpassword', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    yield 'postgres' => new PDO('pgsql:host=127.0.0.1;dbname=test', 'testuser', 'testpassword', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
}

printf("PHP %s\n", PHP_VERSION);
echo "Engine     | Int        | Float      | Decimal\n";
echo "-----------+------------+------------+-----------\n";

foreach (provideDSNs() as $engine => $pdo) {
    echo str_pad($engine, 10);

    $pdo->exec(<<< 'SQL'
        CREATE TABLE types_test (
            id INT NOT NULL PRIMARY KEY,
            some_float FLOAT NOT NULL,
            some_decimal DECIMAL(10, 2)
        )
    SQL);
    $pdo->exec("INSERT INTO types_test VALUES (1, 1.3, 1.3)");
    foreach ($pdo->query('SELECT * FROM types_test')->fetch(PDO::FETCH_NUM) as $value) {
        printf(' | %s', str_pad(json_encode($value, JSON_THROW_ON_ERROR), 10));
    }
    echo "\n";
    $pdo->exec('DROP TABLE types_test');
    unset($pdo);
}


Expected result:
----------------
PHP 8.0.10
Engine     | Int        | Float      | Decimal
-----------+------------+------------+-----------
sqlite     | "1"        | "1.3"      | "1.3"     
mysql      | "1"        | "1.3"      | "1.30"    
postgres   | 1          | "1.3"      | "1.30"   

Actual result:
--------------
PHP 8.1.0-dev
Engine     | Int        | Float      | Decimal
-----------+------------+------------+-----------
sqlite     | 1          | 1.3        | 1.3       
mysql      | 1          | 1.3        | "1.30"    
postgres   | 1          | "1.3"      | "1.30"   

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-08-29 11:15 UTC] me at derrabus dot de
-Summary: PDO_sqlite +Summary: PDO_sqlite: DECIMAL values from results are cast to float
 [2021-08-29 11:15 UTC] me at derrabus dot de
I've updated the title to something more meaningful.
 [2021-08-29 11:19 UTC] me at derrabus dot de
# A docker-compose.yml that sets up the environment I used:

version: '3.6'

services:
    mysql:
        image: 'mysql'
        ports:
            - '3306:3306'
        environment:
            - MYSQL_DATABASE=test
            - MYSQL_USER=testuser
            - MYSQL_PASSWORD=testpassword
            - MYSQL_RANDOM_ROOT_PASSWORD=yes
    postgres:
        image: 'postgres'
        ports:
            - '5432:5432'
        environment:
            - POSTGRES_DB=test
            - POSTGRES_USER=testuser
            - POSTGRES_PASSWORD=testpassword
 [2021-08-29 12:05 UTC] cmb@php.net
SQLite3 is special, since it has no strong typing.  Instead it
uses concepts called "type affinity" and "storage class".  If you
declare a column of type DECIMAL, it is actually treated as having
NUMERIC type affinity, and numbers are stored either as INTEGER or
REAL.  So there is no way of having precise decimals anyway.
You'd need to do the scaling yourself, and declare the column as
INT (or anything that forces INTEGER type affinity).

See also <https://www.sqlite.org/datatype3.html>.
 [2021-08-29 13:13 UTC] me at derrabus dot de
So in other words, PDO could not tell apart DECIMAL from REAL in result sets because SQLite does not provide that information?
 [2021-08-29 14:04 UTC] cmb@php.net
Yes[1].  But even in older PHP versions, the returned string was
not necessarily exact, because SQLite3 internally stores the value
as IEEE 754 binary64 (aka. double).

[1] <https://github.com/php/php-src/blob/php-8.1.0beta3/ext/pdo_sqlite/sqlite_statement.c#L267-L299>.
 [2021-09-01 11:06 UTC] cmb@php.net
-Type: Bug +Type: Documentation Problem
 [2021-09-01 11:06 UTC] cmb@php.net
Not sure how to proceed here.  Switching to doc problem might be
appropriate.
 [2021-09-02 10:02 UTC] me at derrabus dot de
Agreed. The new behavior makes sense to me, given that SQLite emulates decimals using floats. Thank you for the insights.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sat Nov 27 12:03:14 2021 UTC