teableio
Formula engine: ERR 22P02 "invalid input syntax for type double precision" when computing lookup-based currency fields
Describe the bug
When updating (PATCH) a record that contains formula fields depending on lookup-based currency values, the Teable backend fails with a Postgres error:
ERROR: invalid input syntax for type double precision: "3.7525002300010774+35"
This malformed value does not exist anywhere in the user data.
All values shown in the UI are normal decimals (e.g. 3752.00).
The invalid string is generated internally during SQL formula evaluation.
The issue occurs only when a formula aggregates multiple linked currency values.
To Reproduce
Steps to reproduce:
- Create a table A (Invoices) with a numeric/currency field (e.g.
amount). - Create a table B (Planning) with a link-to-multiple-records pointing to A.
- Link 6–8 invoice records to a single Planning record.
- Add a formula field on Planning that computes a total using the lookup values
(e.g.SUM(lookup('Fatturato'))). - Perform a PATCH update on that Planning record via API.
Result:
A backend error:
PrismaClientKnownRequestError
22P02: invalid input syntax for type double precision
with an internally generated string such as:
"3.7525002300010774+35"
Expected behavior
Formula evaluation should correctly aggregate lookup values and preserve valid numeric formats.
No invalid numeric string should be generated during SQL evaluation.
Screenshots
(Available if needed — omitted here to avoid exposing real data.)
Client
- OS: macOS
- Browser: Chrome
- Version: Latest stable
Platform
Self hosted
Additional context
- All user-facing values are valid decimals (
5250.00,4000.00,3752.00, etc.). - No field contains scientific notation or malformed numbers.
- The malformed value appears only inside the SQL generated by Teable’s formula engine.
Relevant SQL pattern:
REGEXP_REPLACE(
(jsonb_array_elements_text(...)::text),
'[^0-9.+-]',
'',
'g'
)::double precision
This transformation can turn a normal decimal into something internally expanded like:
3.7525002300010774e+35
which, after regex stripping, becomes:
3.7525002300010774+35
→ causing Postgres error 22P02.
- Duplicating the same record and re-linking the invoices sometimes avoids the error, suggesting an internal formula-engine evaluation bug rather than incorrect data.
If needed, I can provide the full SQL trace or a sanitized reproducible dataset.
