Why MySQL decimal fields are retrieved like strings while double are not?

If I am not mistaken Laravel does nothing with the values (unless you add a $casts property)

I supposePDO gives it you this way. Can you confirm that by doing a plain PDO query?

$stmt = \DB::getPdo()->prepare("SELECT * FROM `test` LIMIT 1");
$stmt->execute();
dump($stmt->fetch());

Maybe a related ticket here: https://laracasts.com/discuss/channels/eloquent/decimal-or-integer-columns-are-returned-as-strings

Thank you very much for the fast answer, you are right, querying directly with PDO still has this behavior. It's not Eloquent-level:

>>> $stmt = \DB::getPdo()->prepare("SELECT * FROM `tests` LIMIT 1");
=> PDOStatement {#700
     +queryString: "SELECT * FROM `tests` LIMIT 1",
   }
>>> $stmt->execute();
=> true
>>> dump($stmt->fetch());
array:6 [
  "id" => 1
  0 => 1
  "decimal_value" => "8.22"
  1 => "8.22"
  "double_value" => 8.22
  2 => 8.22
]

I've made another test by creating just a simple php script and executing it on the the terminal, the code is:

<?php
$dbHost = "localhost";
$dbUser = "someUser";
$dbPass = "somePassword";
$dbName = "test";

$pdo = new PDO('mysql:host='.$dbHost.';dbname='.$dbName, $dbUser, $dbPass);
$q = $pdo->prepare("SELECT decimal_value, double_value FROM tests");
$q->execute();
foreach($q as $row) {
  var_dump($row);
}

and the output is:

array(4) {
  ["decimal_value"]=>
  string(4) "8.22"
  [0]=>
  string(4) "8.22"
  ["double_value"]=>
  string(4) "8.22"
  [1]=>
  string(4) "8.22"
}

Weird, I will continue investigating the issue.

use the mysqlnd php driver.

https://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo

Thanks for the anwser @Cronix however I'm already using it, just in case I've executed this on tinker to make sure:

>>> use App\Test;
>>> app()->make(Test::class)->first();
=> App\Test {#725
     id: 1,
     decimal_value: "8.22",
     double_value: 8.22,
   }
>>> $stmt = \DB::getPdo()->getAttribute(PDO::ATTR_CLIENT_VERSION);
=> "mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $"

Did you see the pdo options you can set further down the link?

Thanks @Cronix, I have now read all replies on the link you attached and I think I've got the answer:

As this reply says:

PDO uses emulated prepared statements by default for all MySQL connections. That's why the php script on my second reply was returning both values as strings, differing from the Eloquent query result.

If I modify the script to this (note the two attributes being set):

$pdo = new PDO('mysql:host='.$dbHost.';dbname='.$dbName, $dbUser, $dbPass);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
var_dump($pdo->getAttribute(PDO::ATTR_CLIENT_VERSION));
$q = $pdo->prepare("SELECT id, decimal_value, double_value FROM tests");
$q->execute();
foreach($q as $row) {
  var_dump($row);
}

Now the output is like this:

array(6) {
  ["id"]=>
  int(1)
  [0]=>
  int(1)
  ["decimal_value"]=>
  string(4) "8.22"
  [1]=>
  string(4) "8.22"
  ["double_value"]=>
  float(8.22)
  [2]=>
  float(8.22)
}

Which is the same thing I was having with Eloquent (seems that the queries made from Eloquent have EMULATE_PREPARES as false?). Still, the double value is being casted to a number while the decimal value is not.

Now, reading carefully the sub-answers on the stack overflow thread there is this:

"Note that DECIMAL type will still be sent as string even with this configuration that is done on purpose to prevent the decimal from becoming an unreliable float in PHP."

Which is confirmed by this Bug report.

On why the double value from MySQL can be safely casted into a PHP float, and why a decimal not I won't dive right now, but is someone has some links or types knowledge it would be nice.

I guess the issue is resolved, I've learned something new tonight, thanks everyone!