pdo_mysql not returning int for sum aggregate function

[SamMousa]

Description

The following code:

<?php
        var_dump($db->pdo->query(<<<SQL
            select sum(`id`), avg(`id`), json_type(sum(`id`)) from (
                select 1 as `id`
                union
                select 2 as `id`
            ) as sub
        SQL, \PDO::FETCH_ASSOC)->fetch());

Resulted in this output:

array(3) {
  'sum(`id`)' =>
  string(1) "3"
  'avg(`id`)' =>
  string(6) "1.5000"
  'json_type(sum(`id`))' =>
  string(7) "INTEGER"
}

But I expected this output instead:

array(6) {
  'sum(`id`)' =>
  int(3)
  'avg(`id`)' =>
  float(1.5)
  'json_array(sum(`id`))' =>
  int(3)
  'json_type(sum(`id`))' =>
  string(7) "INTEGER"
}

It seems the sum function does not result in a properly typed value. Upon further investigation:

• avg also exhibits this behavior
• I added a json_type column (which could be mariadb only) to see what the database engine thinks of the type
• tested on version 8.3.? as well with mysql

PHP Version

8.2.20

Operating System

docker(alpine)

[Girgias]

Are you using the PDO::ATTR_STRINGIFY_FETCHES attribute or not?

[SamMousa]

I'm not, I should have included that explicitly in my test case. I noticed it in an result set where half of my fields were proper int but other fields using sum() were strings.

[Girgias]

Okay, it's just to be sure that the attribute is not the reason. :)

[SamMousa]

Another test case that illustrates thatmin is properly supported.

<?php
var_dump($db->pdo->query(<<<SQL
            select min(`id`), sum(`id`), avg(`id`), json_type(sum(`id`)) from (
                select 1 as `id`
                union
                select 2 as `id`
            ) as sub
        SQL, \PDO::FETCH_ASSOC)->fetch());

array(4) {
  'min(`id`)' =>
  int(1)
  'sum(`id`)' =>
  string(1) "3"
  'avg(`id`)' =>
  string(6) "1.5000"
  'json_type(sum(`id`))' =>
  string(7) "INTEGER"
}

[damianwadley]

Thing is that SUM and AVG both return either DECIMAL- or DOUBLE-typed values, depending on the arguments. Either way means it's not a plain integer.
For MIN, it's not clear from the docs but I suspect it simply returns whatever the minimum value was, be that an integer or float or whatever.

So I think this is working correctly...

[SakiTakamachi]

@SamMousa

What is the var type if exec the following query?

SELECT CAST(1 AS SIGNED)

Will it be an int?

edit:
Regardless of the attribute value, if you are using libmysql instead of mysqlnd, all return values ​​will be string.

If integer types are also retrieved as strings, we recommend checking if the driver you are using is mysqlnd (you can check this with phpinfo).

[SamMousa]

What is the var type if exec the following query?

These are ints.

If integer types are also retrieved as strings, we recommend checking if the driver you are using is mysqlnd (you can check this with phpinfo).

This is not the case as in my previous message I already showed that a min(id) select clause will return an int.
It has already been established that some type casting works correctly.

Thing is that SUM and AVG both return either DECIMAL- or DOUBLE-typed values, depending on the arguments. Either way means it's not a plain integer.

How could we confirm this? If I look for "formal" docs on SUM I find some references that the return type should be int if the expression type is int. (Which makes sense, the same as min and max, these aggregate functions have the same type as their argument).
Hmm, I found mysql --column-type-info:

Field   1:  `sum(id)`
Org_field:  ``
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     33
Max_length: 10
Decimals:   0
Flags:      BINARY NUM 

Some more testing shows that min(1.5) returns NEWDECIMAL, min(1) returns LONGLONG. Seems this behavior is inconsistent in MySQL / MariaDB itself.

[damianwadley]

How could we confirm this?

MariaDB's docs don't say anything about return types, at least that I could see, but MySQL's does:

The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).

When the function returns a DECIMAL then I would expect to see a string in PHP, and when the function returns a plain integer-type then I would expect to see an integer.

[SamMousa]

Yeah I agree; but this is inconsistent with how they deal with min and max which take their type from their argument. This is not a php bug, but a MySQL & MariaDB inconsistency.

[MorganLOCode]

min and max always return a result that is within the same domain as their arguments (seeing as they return one of those arguments); sum and avg can both return values outside that domain and so their return types need to be wider.