DBAL36 - BIGINT documentation misunderstanding

[cizordj]

Bug Report

Q	A
Version	3.6.5

Summary

Please rephrase the explanation in the docs about the BIGINT datatype.

Current behaviour

The current statements lead to misinterpretation:

<!-- dbal/docs/en/reference/types.rst:90 -->
Values retrieved from the database are always converted to PHP's ``string`` type
or ``null`` if no data is present.

<!-- dbal/docs/en/reference/types.rst:95 -->
For compatibility reasons this type is not converted to an integer

Which is not true.

We have many entities mapped with BIGINT and BIGSERIAL in a PostgreSQL database
and doctrine always casts their value to a regular PHP integer. We use the 64 bit architecture
and the primary keys are mapped this way.

<?php
class User {
    #[ORM\Id]
    #[ORM\GeneratedValue(strategy: 'SEQUENCE')]
    #[ORM\SequenceGenerator(
        sequenceName: 'user.user_id_user_seq',
        allocationSize: 1,
        initialValue: 1
    )]
    #[ORM\Column(name: 'id_user', type: Types::BIGINT)]
    private ?int $id = null;
}

According to the PostgreSQL's documentation the maximum number for a BIGINT field is 9223372036854775807 which is the same as the PHP_INT_MAX.

How to reproduce

• Make an entity whose primary key represents a BIGINT type in the database.
• Run the respective migration
• Add a row to the table via a SQL script
• Fetch that entity using that entity's repository via Symfony Commands or Regular Controllers
• Debug the datatype via dd() or let it serialize it through a JsonResponse.

You'll notice that the value comes as a PHP integer.

You can also add another row to the table with the maximum possible value, see the value of PHP_INT_MAX and make an insertion to the database.
Fetch all the entities and you can see that the value still comes as a regular integer type.

Due to the limitations of JSON, things start to get really weird for numbers like this, so you can only trust the dd() output in this case.

Expected behaviour

The docs should say that the values retrieved from the database are always converted to PHP's int type or null if no data is present and display a notice about it being cast to string only in legacy 32-bit platforms.

Btw why have you guys changed your minds? #2976

[derrabus]

In your example, the ID is cast to an integer because your class uses int as a native property type. The documentation is correct, see:

dbal/src/Types/BigIntType.php

Line 48 in 17d7baf

return $value === null ? null : (string) $value;

But given that 32bit systems are rather rare, we could change this behavior in 4.0. Up for a PR?

[derrabus]

According to the PostgreSQL's documentation the maximum number for a BIGINT field is 9223372036854775807 which is the same as the PHP_INT_MAX.

Note that MySQL's BIGINT UNSIGNED can still exceed PHP_INT_MAX.

[cizordj]

But given that 32bit systems are rather rare, we could change this behavior in 4.0. Up for a PR?

Yes, but what about the UNSIGNED integers?

[derrabus]

But given that 32bit systems are rather rare, we could change this behavior in 4.0. Up for a PR?

Yes, but what about the UNSIGNED integers?

I would simply remove the string cast and return the value as we receive it from the driver. This is how we've always done it for 32bit integers.

edit: I was wrong. We can compare the value returned from the database with PHP_INT_MAX and perform the int cast only when it's safe to do so. Otherwise, we return the value as is.

[cizordj]

So we'll be basically returning two types depending on the database, right? integer when it's bigint signed and float when it's bigint unsigned. Doesn't that make the behavior unpredictable? How about we create another dedicated type for unsigned big integers?
I guess we shouldn't support non-standard SQL types, right? EDIT: I mean the unsigned big integers

[derrabus]

float when it's bigint unsigned

No, we never cast integers to float.

[cizordj]

Not you but PHP does.

php > var_dump((int) PHP_INT_MAX + 1);
float(9.223372036854776E+18)

[cizordj]

In my opinion it got a little confused.

pull-6143

The same class is returning two distinct types but it's saying that it returns Integer in the getBindingType.

Is this is a case for a new class?

[derrabus]

Not you but PHP does.

php > var_dump((int) PHP_INT_MAX + 1);
float(9.223372036854776E+18)

This is not a type-cast. It's a sum operation with a float result. I don't see how this relates to my statement or the issue we're discussing here.

[cizordj]

Alright, no problem.

[stof]

@cizordj the binding type is not about the type of the PHP representation. It is about the way the value should be interpreted in query parameters (so that the database knows how to cast it when executing a prepared statement)

[derrabus]

I think, we can close this issue. @ondrejmirtes, I've answered your comment on #6177 already. Let's keep the discussion in one place.

[cizordj]

@ondrejmirtes if you know for sure your integer is signed then you don't need to declare as string|int

[ondrejmirtes]

@cizordj Can you tell me the conditions for which Orm\Column with bigint type can only ever be int, and when it can also be string?

[cizordj]

I'll answer you there #6177

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.