An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range

[abdounikarim]

Bug Report

Hey, on a fresh symfony application, when I tried to see a specific entity with a big value id (here, I used the value 1280187389103) and I got this error:

An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 7 ERROR: value "1280187389103" is out of range for type integer CONTEXT: unnamed portal parameter $1 = '..'

Q	A
Version	3.10.3
Previous Version if the bug is a regression	x.y.z

Summary

I just tried to look for an unexisting entity with a big value, and expected to have something like : "Entity not found"

Current behavior

I put there the stack trace:

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 7 ERROR:  value "172901872891038" is out of range for type integer
CONTEXT:  unnamed portal parameter $1 = '...'

  at vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:87
  at Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(object(Exception), object(Query))
     (vendor/doctrine/dbal/src/Connection.php:1976)
  at Doctrine\DBAL\Connection->handleDriverException(object(Exception), object(Query))
     (vendor/doctrine/dbal/src/Connection.php:1918)
  at Doctrine\DBAL\Connection->convertExceptionDuringQuery(object(Exception), 'SELECT t0.id AS id_1 FROM foo t0 WHERE t0.id = ?', array('172901872891038'), array('integer'))
     (vendor/doctrine/dbal/src/Connection.php:1111)
  at Doctrine\DBAL\Connection->executeQuery('SELECT t0.id AS id_1 FROM foo t0 WHERE t0.id = ?', array('172901872891038'), array('integer'))
     (vendor/doctrine/orm/src/Persisters/Entity/BasicEntityPersister.php:740)
  at Doctrine\ORM\Persisters\Entity\BasicEntityPersister->load(array('id' => '172901872891038'), null)
     (vendor/doctrine/orm/src/Persisters/Entity/BasicEntityPersister.php:758)
  at Doctrine\ORM\Persisters\Entity\BasicEntityPersister->loadById(array('id' => '172901872891038'))
     (vendor/doctrine/orm/src/EntityManager.php:366)
  at Doctrine\ORM\EntityManager->find('App\\Entity\\Foo', array(), null, null)
     (vendor/doctrine/orm/src/EntityRepository.php:86)
  at Doctrine\ORM\EntityRepository->find('172901872891038', null, null)
     (vendor/doctrine/doctrine-bundle/src/Repository/ServiceEntityRepositoryProxy.php:61)
  at Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepositoryProxy->find('172901872891038')
     (vendor/symfony/doctrine-bridge/ArgumentResolver/EntityValueResolver.php:129)
  at Symfony\Bridge\Doctrine\ArgumentResolver\EntityValueResolver->find(object(EntityManager), object(Request), object(MapEntity), object(ArgumentMetadata))
     (vendor/symfony/doctrine-bridge/ArgumentResolver/EntityValueResolver.php:69)
  at Symfony\Bridge\Doctrine\ArgumentResolver\EntityValueResolver->resolve(object(Request), object(ArgumentMetadata))
     (vendor/symfony/http-kernel/Controller/ArgumentResolver/TraceableValueResolver.php:37)
  at Symfony\Component\HttpKernel\Controller\ArgumentResolver\TraceableValueResolver->resolve(object(Request), object(ArgumentMetadata))
     (vendor/symfony/http-kernel/Controller/ArgumentResolver.php:91)
  at Symfony\Component\HttpKernel\Controller\ArgumentResolver->getArguments(object(Request), array(object(FooController), 'show'), object(ReflectionMethod))
     (vendor/symfony/http-kernel/Controller/TraceableArgumentResolver.php:33)
  at Symfony\Component\HttpKernel\Controller\TraceableArgumentResolver->getArguments(object(Request), array(object(FooController), 'show'), object(ReflectionMethod))
     (vendor/symfony/http-kernel/HttpKernel.php:175)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:76)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:182)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (vendor/symfony/runtime/Runner/Symfony/HttpKernelRunner.php:35)
  at Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run()
     (vendor/autoload_runtime.php:29)
  at require_once('/Users/abdounikarim/test/22003-doctrine-numeric-value-reproducer/vendor/autoload_runtime.php')
     (public/index.php:5)

Expected behavior

I think we must have an error message saying this entity was not found.

How to reproduce

I created a reproducer here: https://github.com/abdounikarim/22003-doctrine-numeric-value-reproducer, it's just a fresh symfony application with nothing fancy, you can read the README.md to see the steps to reproduce the issue.

Hope this helps 🙏

[greg0ire]

I think it might make sense to introduce an OutOfRange exception that you could catch and transform in a 404 in the context of an HTTP request. Then https://github.com/doctrine/dbal/blob/4.3.x/src/Driver/API/PostgreSQL/ExceptionConverter.php would need to be changed to convert to this exception, as well as other converters for other databases.

@morozov @derrabus , what do you think?

[morozov]

Introducing a specialized exception type sounds good. Not sure about the exact name though (I don't have a strong opinion). It looks like OutOfRange is about an illegal value that can be detected at compile time (i.e. is declared in the code, not our case), while OutOfBounds is a runtime exception (looks more appropriate).

As for the HTTP translation, it should be a 400 (Bad Request), not a 404.

[greg0ire]

@abdounikarim , do you want to give that a try?

[derrabus]

It's the parameter that is out of range, right? That's wild. Which Postgres driver is this exactly?

The stack trace of the inner exception would've been a lot more helpful than the outer one.

[morozov]

It's the parameter that is out of range, right? That's wild.

I can reproduce the issue as follows (note that the value is a string, and this is the default binding type in the DBAL):

CREATE TABLE t (
    id SMALLINT
);

INSERT INTO t VALUES ('1280187389103');
-- [22003] ERROR: value "1280187389103" is out of range for type integer

If I pass an integer, it will also fail, but with a different error message (same error code):

INSERT INTO t VALUES (1280187389103)
-- [22003] ERROR: integer out of range

[abdounikarim]

@greg0ire what can I do to help you ?

For other databases, how did you do to handle this error ?

Thanks

[greg0ire]

@abdounikarim you can implement the change described in https://github.com/doctrine/dbal/issues/7193#issuecomment-3392986583

I don't know if that error is handled for other databases, that's probably something you can figure out with a failing test. For the failing test, you can use code from https://github.com/doctrine/dbal/issues/7193#issuecomment-3403317217