Search code examples
phpmysqlpdosumsqldatatypes

Why SUM(`column`) returns a string instead of an integer?

I'm using Laravel and have a query that selects using DB::raw() SUM() of a column:

DB::raw("SUM(points) as game_points")

I have installed mysqldn and AFAIK Laravel uses PDO.

But game_points is a string, no matter what type of the column is. (It's an integer column)

Also if I do:

DB::raw("COUNT(id) as foo_bar")

foo_bar is returned as an integer.

Solution

  • It's neither Laravel or PDO issue.

    According to MySQL manual, SUM() returns a DECIMAL value for exact-value arguments (integer or DECIMAL). And the only way to represent DECIMAL type in PHP is string, for two reasons:

    • it can overflow the PHP's int type, being bigger than PHP_INT_MAX.
    • also, in case the returned value being a decimal number, it can lose precision due to inherently imprecise nature of floating point numbers (for example DECIMAL(10,2) can accurately store 0.1, while the closest thing php's native float-type can store is 0.1000000000000000055511151231257827021181583404541015625 )

    due to these precautions the value is returned as string, and you are supposed to convert it manually according to the expecting value - either using standard PHP type casting or using some dedicated math functions such as bcmath.