- EspoCRM version: 9.3.8
- PHP version: 8.4.21
- Database: PostgreSQL 18.3
- Extensions involved: none — reproducible with core code only (see below)
Steps to Reproduce
1. On a PostgreSQL-backed instance, run the following against any core entity whose `id` (or any other varchar/text) attribute is being filtered with an `IN`-style array, where at least one element of that array is a native PHP `int` instead of a `string`:
Code:
$entityManager->getRDBRepository('Contact')
->where([
'id' => [123456, 'anExistingContactId'],
])
->find();
(`123456` is a plain PHP int; it does not need to correspond to a real record — the error happens during SQL generation, before execution against real data.)
2. Observe the exception thrown when the query executes.
Expected Result
The query executes normally. Every element of the `IN (...)` list is quoted as a string literal, because the target column (`contact.id`, `character varying`) is a string type — regardless of the PHP-side type of the value being compared against it.
Actual Result
PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = integer
LINE 1: ...WHERE "contact"."id" IN (123456, 'anExistingContactId')
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
LINE 1: ...WHERE "contact"."id" IN (123456, 'anExistingContactId')
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
Root Cause
`Espo\ORM\QueryComposer\BaseQueryComposer::quote() ` decides whether to quote a value purely based on the PHP type of the value, not the database type of the column it is being compared against:
On MySQL this goes unnoticed because MySQL implicitly coerces between `varchar` and `integer` in comparisons. PostgreSQL does not, and rejects the query outright.
EspoCRM's own default ID generator can produce IDs that are *purely numeric strings* purely by chance.
Suggested Fix
`quote()` (or the calling comparison-composition logic) should resolve the target column's declared type via `Espo\ORM\Metadata` for the attribute being compared, and quote the value according to that column type rather than the PHP type of the value. Alternatively, the value could be cast to a string whenever the target attribute type is `varchar`/`text`/`id`/`foreignId`, before quoting.
Workaround
We work around this in application code by explicitly casting ID arrays to strings before passing them into `where()`/`IN` filters, e.g. `array_map('strval', array_keys($idMap))`. This is safe but has to be applied manually at every call site that builds an ID array this way.
Comment