Numeric/Decimal type return as float

[badmansan]

Version: v3.0.5

Bug Description

Numeric/Decimal type return as float instead string

Steps To Reproduce

-- mysql
CREATE TABLE `balance` (
  `id` int(11) NOT NULL,
  `balance` decimal(12,4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `balance` (`id`, `balance`) VALUES
(1, 12.1234);

-- postgresql
CREATE TABLE "public"."balance" (
    "id" integer NOT NULL,
    "balance" numeric(12,4) NOT NULL
) WITH (oids = false);
INSERT INTO "balance" ("id", "balance") VALUES
(1, 12.1234);

<?php
$pdo = new PDO('pgsql:host=localhost;dbname=test', 'postgres', 'postgres');
//$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT balance FROM balance WHERE id = ?');
$stmt->execute([1]);
var_dump($stmt->fetchColumn());

$db = new Nette\Database\Connection('pgsql:host=localhost;dbname=test', 'postgres', 'postgres');
//$db = new Nette\Database\Connection('mysql:host=localhost;dbname=test', 'root', 'root');

$res = $db->query('SELECT balance FROM balance WHERE id = ?', 1);
var_dump($res->fetchField());

after run php code we got

(string) '12.1234' (float) 12.1234

Expected Behavior

(string) '12.1234' (string) '12.1234'

Possible Solution

I think float must return only if db field type is float or double

[dg]

Why should it return decimal as a string?

[badmansan]

Because these types are generally used for storing money, and we never use floats for money. Php does not have a built-in money format, so the only way out is a string. At first example with PDO you can see the right way.

[diegosardina]

To be more precise:

Because PHP doesn't support the decimal type that follows fixed-point arithmetic (instead of floating-point arithmetic).

Currently in PHP for serious financial stuff (such as accounting software) the only reliable way is string + BCMath (indeed bc* functions work on strings).

php > var_dump(3 - 2.99);
float(0.0099999999999998)

php > var_dump(bcsub(3, 2.99, 2));
string(4) "0.01"

Furthermore, usually decimal/numeric types can reach very large numbers (PostgreSQL numeric type range is described as "up to 131072 digits before the decimal point; up to 16383 digits after the decimal point").

String + BCMath is the only way to deal with decimals.

[dg]

I understand, but it is huge BC break…

[badmansan]

I understand, but it is huge BC break…

May be it be possible to add a mode selection as an option? Something like

$db = new Nette\Database\Connection(...);
$db->setDecimalMode(Mode::AS_STRING);

The string will only be returned if set explicitly, and this will save BC

[diegosardina]

A more general way is being able to change the row normalization with a custom implementation.

There is already a PR here #138.

[milo]

When working with money, I'm using explicit type casting to string SELECT money::text FROM ... as a workaround in PostgreSQL.

[dg]

I added the option to configure row normalizer to disable conversion of numeric types to floats.

$db = new Nette\Database\Connection(...);
$db->setRowNormalizer((new Nette\Database\RowNormalizer)->skipNumeric());