Bug #81397 [NEW]: PDO_sqlite

From: me at derrabus dot de Operating system: macOS 11.5 PHP version: 8.1.0beta3 Package: PDO SQLite Bug Type: Bug Bug description:PDO_sqlite 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" -- Edit bug report at https://bugs.php.net/bug.php?id=81397&edit=1 -- Fix committed: https://bugs.php.net/fix.php?id=81397&r=fixed Fixed in release: https://bugs.php.net/fix.php?id=81397&r=alreadyfixed Need backtrace: https://bugs.php.net/fix.php?id=81397&r=needtrace Need Reproduce Script: https://bugs.php.net/fix.php?id=81397&r=needscript Try newer version: https://bugs.php.net/fix.php?id=81397&r=oldversion Not developer issue: https://bugs.php.net/fix.php?id=81397&r=support Expected behavior: https://bugs.php.net/fix.php?id=81397&r=notwrong Not enough info: https://bugs.php.net/fix.php?id=81397&r=notenoughinfo Submitted twice: https://bugs.php.net/fix.php?id=81397&r=submittedtwice register_globals: https://bugs.php.net/fix.php?id=81397&r=globals PHP version support discontinued: https://bugs.php.net/fix.php?id=81397&r=phptooold Daylight Savings: https://bugs.php.net/fix.php?id=81397&r=dst IIS Stability: https://bugs.php.net/fix.php?id=81397&r=isapi Install GNU Sed: https://bugs.php.net/fix.php?id=81397&r=gnused Floating point limitations: https://bugs.php.net/fix.php?id=81397&r=float No Zend Extensions: https://bugs.php.net/fix.php?id=81397&r=nozend MySQL Configuration Error: https://bugs.php.net/fix.php?id=81397&r=mysqlcfg