nette / database

💾 A database layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle.
https://doc.nette.org/database
Other
492 stars 105 forks source link

PostgreSQL: Floating point value in scientific notation with trailing zero gets changed #307

Open NTSFka opened 2 months ago

NTSFka commented 2 months ago

Version: 3.2, 3.1.7, 3.1, ...

Bug Description

Row normalizer changes value of floating point value in scientific notation (e.g. 1E-10) with exponent that ends with zeroes. This is only done when PDO driver returns floating point value as string (PostgreSQL) and not as float (MySQL and SQLite3).

Steps To Reproduce

Can be reproduced by adding this code to ResultSet.normalizeRow.postgre.phpt.

$res = $connection->query('SELECT CAST(1.2E-10 AS double precision) AS value');

Assert::same([
    'value' => 1.2E-10,
], (array) $res->fetch());

It fails with:

Driver: pgsql
Failed: ['value' => 0.12] should be 
    ... ['value' => 1.2E-10]

Database returns value as '1.2E-10' but row normalizer changes value to '1.2E-1' by removing trailing zeroes.

Problem is caused by first block of handling float and decimal values (from v3.2):

} elseif ($type === IStructure::FIELD_FLOAT || $type === IStructure::FIELD_DECIMAL) {
    if (is_string($value) && ($pos = strpos($value, '.')) !== false) {
        $value = rtrim(rtrim($pos === 0 ? "0$value" : $value, '0'), '.');
    }

    $row[$key] = (float) $value;

} ...

Expected Behavior

Floating point values shouldn't be changed.

Possible Solution

1) Remove code that remove trailing zeroes from string representation of floating point values - tests didn't failed. 2) Modify condition in row normalizer to ignore string in scientific notation.

Before 3.1.5 there was code that returns numeric values that are not possible to represent as floating point as string (if I understand correctly) but that was removed in order to fix #289. In result I see no point of having that code because PHP's cast operator can handle conversion and floating point and decimal values are never returned as string after 3.1.5.