Returning floats and not strings with PDO and recursive queries?

[NotionCommotion]

Why does the first query return floats yet the second return strings?  How can I make the second query return floats?  Or will I just need to manually use floatval()?

 

<?php
error_reporting(E_ALL);
ini_set('display_startup_errors', 1);
ini_set("log_errors", 1);
ini_set('display_errors', 1);
openlog('PublicAPI', LOG_CONS | LOG_NDELAY | LOG_PID, LOG_USER | LOG_PERROR);

$config = parse_ini_file(__DIR__ . '/../config.ini', true, INI_SCANNER_TYPED);
$db = $config['mysql'];
$pdo = new PDO("mysql:host={$db['host']};dbname={$db['dbname']};charset={$db['charset']}", $db['username'], $db['password'], array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
));

$sql = <<<EOL
  SELECT   p1.id,
           sum(p2.slope*p2.value+p2.intercept)     value
         FROM   points p1
         INNER JOIN points_custom_has_points pchp ON pchp.pointsCustomId=p1.id
         INNER JOIN points p2 ON p2.id=pchp.pointsId
         WHERE  p1.id IN (?,?)
  GROUP BY p2.id;
EOL;
$stmt = $pdo->prepare($sql);
$stmt->execute([248, 405]);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$rs = $stmt->fetchAll();
var_dump($rs);
echo("<br><br>");

$sql = <<<EOL
 WITH recursive t
AS
  (
         SELECT id,
                id pointsid,
                type,
                cast(0 AS DECIMAL(12,4)) value,
                cast(0 AS DECIMAL(12,4)) prevvalue,
                cast(1 AS DECIMAL(12,4)) slope,
                cast(0 AS DECIMAL(12,4)) intercept
         FROM   points
         WHERE  id IN (?,?)
         UNION ALL
         SELECT     t.id,
                    pchp.pointsid,
                    p.type,
                    p.value,
                    p.prevvalue,
                    p.slope,
                    cast(p.intercept AS DECIMAL(12,4)) intercept
         FROM       t
         INNER JOIN points_custom_has_points pchp
         ON         pchp.pointscustomid=t.pointsid
         INNER JOIN points p
         ON         p.id=pchp.pointsid )
  SELECT   id,
           sum(slope*value+intercept)     value
  FROM     t
  WHERE    type='real'
  GROUP BY id;
EOL;
$stmt = $pdo->prepare($sql);
$stmt->execute([248, 405]);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$rs = $stmt->fetchAll();
var_dump($rs);

output:

array(4) { [0]=> array(2) { ["id"]=> int(405) ["value"]=> float(46094.80078125) } [1]=> array(2) { ["id"]=> int(405) ["value"]=> float(74616) } [2]=> array(2) { ["id"]=> int(248) ["value"]=> float(11.223400115967) } [3]=> array(2) { ["id"]=> int(248) ["value"]=> float(4.3117699623108) } } 

array(2) { [0]=> array(2) { ["id"]=> int(248) ["value"]=> string(11) "15.53520000" } [1]=> array(2) { ["id"]=> int(405) ["value"]=> string(15) "120710.80080000" } }

 

MariaDB [testing]> explain points;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| idPublic       | int(11)     | NO   | MUL | 0       |                |
| accountsId     | int(11)     | NO   | MUL | NULL    |                |
| name           | varchar(45) | NO   | MUL | NULL    |                |
| value          | float       | YES  |     | NULL    |                |
| prevValue      | float       | YES  |     | NULL    |                |
| units          | varchar(45) | YES  |     | NULL    |                |
| type           | char(8)     | NO   | MUL | NULL    |                |
| slope          | float       | NO   |     | 1       |                |
| intercept      | float       | NO   |     | 0       |                |
| tsValueUpdated | datetime    | YES  |     | NULL    |                |
| sourceTypeId   | tinyint(4)  | YES  | MUL | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

MariaDB [testing]> explain points_custom_has_points;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| pointsCustomId | int(11)    | NO   | PRI | NULL    |       |
| pointsId       | int(11)    | NO   | PRI | NULL    |       |
| sign           | tinyint(4) | NO   | MUL | 1       |       |
+----------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [testing]>

 

[kicken]

DECIMAL types are for fix precision.  A float on the other hand may be subject to rounding if the value is not able to be represented exactly.  In order to keep the precision and avoid potential rounding issues the decimals have to be represented as strings.  You'll also need to use something like BC or GMP to do any math on them if you wanted to avoid accidental rounding.

 

[NotionCommotion]

18 hours ago, kicken said:

In order to keep the precision and avoid potential rounding issues the decimals have to be represented as strings.

Not sure I understand.

I don't need exact accuracy.  Originally, I didn't type cast the slope value of 1, and it was interpreted as an integer, and had unacceptable accuracy.  I would have liked to type cast as a float, but I don't think one is able to.

[kicken]

12 hours ago, NotionCommotion said:

I don't need exact accuracy.

Maybe you don't, but some people do.  If PHP automatically cast DECIMAL fields to float values, those people would be rather unhappy.  Keeping them as strings is the only way to guarantee that precision isn't lost, so that's what PHP does.   If you don't care, and want them to be floats, you can always cast them that way yourself (or just let PHP auto-cast them when necessary).

In your first query, you do no conversions and your fields are FLOAT values.  As a result, your equation yields a FLOAT result.

In your second query, you convert your fields to DECIMAL values.  If a DECIMAL is involved in an equation, then values are converted to DECIMAL and the result is DECIMAL.

Quote

 

Rules for Conversion on Dyadic Arithmetic Operations

For addition (+), subtraction (-) and multiplication (*), the result data type is chosen as follows:

•     If either of the arguments is an approximate number (float, double), the result is double.
•     If either of the arguments is a string (char, varchar, text), the result is double.
•     If either of the arguments is a decimal number, the result is decimal.
•     If either of the arguments is of a temporal type with a non-zero fractional second precision (time(N), datetime(N), timestamp(N)), the result is decimal.
•     If either of the arguments is of a temporal type with a zero fractional second precision (time(0), date, datetime(0), timestamp(0)), the result may vary between int, int unsigned, bigint or bigint unsigned, depending on the exact data type combination.
•     If both arguments are integer numbers (tinyint, smallint, mediumint, bigint), the result may vary between int, int unsigned, bigint or bigint unsigned, depending of the exact data types and their signs.

 

 

 

[NotionCommotion]

Thanks Kicken,

Yeah, you hear about the computer hacks which steel fractions of pennies every transaction.  Guess they add up.

How would you do this with floats?