Chat Filter SQL Issue

English Forum Issue Report
1

* Describe the bug

When a page-level Filter block’s custom field (type Date) is used as a variable inside a Chart block’s SQL query (Data Visualization / Flow Engine), the bound parameter is sent to PostgreSQL typed as bigint instead of date/timestamp, even though the source custom field is explicitly configured as Date (format Year-Month-Day, no time, no date range) and the filter currently holds a valid value.

This causes the query to fail at the parameter-binding step — before PostgreSQL ever parses the SQL text — so no amount of explicit casting in the query (::date, CAST(... AS DATE), TO_DATE(...)) has any effect, since the mismatch happens before the query text is evaluated.

Additionally, the two variable-reference syntaxes behave very differently, and only one of them resolves to an actual value:

  • {{ ctx.filter.<field> }} (dot-path, no quotes) → always fails with Named bind parameter "$__varN" has no value in the given object., even while the filter block visibly has a valid value selected on the live page. (In some attempts it instead produced the literal string (await ctx.filter).desde inside the query, causing a separate SQL syntax error.)
  • {{ '<field>' }} (field name as a quoted string) → this is the only form that actually resolves to a value. It is what produces the bigint type-mismatch error described above.

So ctx.filter.<field> never even reaches PostgreSQL with a real value — it fails earlier, at the bind-object-assembly step. Only {{ '<field>' }} gets far enough to hit the bigint casting bug.

* Environment

  • NocoBase version: 2.2.4
  • Database type and version: PostgreSQL Postgres 16
  • OS: Linux Ubuntu Linux 24.04.3
  • Deployment Methods: Node create app
  • Docker image version: N/A (not using Docker)
  • NodeJS version: 22.17.0

* How To Reproduce

  1. Use a collection with a real timestamp column, e.g. ordenesdetrabajo.fechaini_prog (PostgreSQL, external schema mantenimientotmb).
  2. Create a page containing:
    • A Filter block with two custom fields, desde and hasta, both configured as field type Fecha/Date, format Año-Mes-Día (Year-Month-Day), “Mostrar hora” (show time) OFF, “Date range” OFF.
    • A Chart block in SQL mode on the same page.
  3. In the chart’s SQL query, reference the filter’s custom fields as variables:
    SELECT TO_CHAR(fechaini_prog, 'YYYY-MM') AS mes, COUNT(*) AS total
FROM mantenimientotmb.ordenesdetrabajo
WHERE fechaini_prog IS NOT NULL
  AND fechaini_prog >= {{ 'desde' }}
  AND fechaini_prog <= {{ 'hasta' }}
GROUP BY 1;
  4. On the live page (not inside the block’s own config preview), set both filter fields to valid dates, e.g. desde = 2025-08-01, hasta = 2026-08-31.
  5. Click “Run query” on the chart.

Expected behavior

The query should run successfully, with desde/hasta bound as date/timestamp values, since the source custom field is explicitly typed as Date and both fields have valid values selected on the page.

Actual behavior

The query fails with:

Query Error
la sintaxis de entrada no es válida para tipo bigint: «2025-08-01 00:00:00.000 -03:00»

(English equivalent: invalid input syntax for type bigint: "2025-08-01 00:00:00.000 -03:00")

The parameter is evidently sent with an implicit bigint/int8 type expectation on the wire, while the actual bytes are a formatted date-time-with-timezone string — a mismatch that occurs regardless of how the SQL text casts or wraps the variable, since the failure happens before the query text is evaluated by PostgreSQL.

Separately (see note above), replacing {{ 'desde' }} / {{ 'hasta' }} in the query above with {{ ctx.filter.desde }} / {{ ctx.filter.hasta }} reproduces a different, earlier failure — every time, regardless of whether the filter has a value selected:

Query Error
Named bind parameter "$__var1" has no value in the given object.

This confirms the ctx.filter.<field> path is not resolved by the SQL-mode variable compiler at all; only the quoted field-name form ({{ 'desde' }}) is recognized, and that form is the one that then hits the bigint casting bug described above.

Screenshots

image
image604×692 12.7 KB

[Attach: 1) Field settings screenshot showing “Tipo de campo: Fecha” for the hasta filter field.

image
image1910×428 18.8 KB

2) Dashboard screenshot showing the Filter block with Desde: 2025-08-01 / hasta: 2026-08-31 set, alongside the Chart’s SQL panel showing the bigint error.]

Logs

Query Error
Named bind parameter "$__var1" has no value in the given object.

Query Error
la sintaxis de entrada no es válida para tipo bigint: «2025-08-01 00:00:00.000 -03:00»

Server-side log excerpt (via @nocobase/plugin-flow-engine):

Named bind parameter "$__var1" has no value in the given object.
  at Query.formatBindParameters (node_modules/sequelize/lib/dialects/abstract/query.js:92:15)
  at AbstractQuery.formatBindParameters (node_modules/sequelize/lib/dialects/abstract/query.js:78:15)
  at Query.formatBindParameters (node_modules/sequelize/lib/dialects/postgres/query.js:31:27)
  at Sequelize.query (node_modules/sequelize/lib/sequelize.js:290:50)
  at _AsyncEmitter.runSQLWithSchema (node_modules/@nocobase/database/lib/database.js:802:29)
  at _AsyncEmitter.runSQL (node_modules/@nocobase/database/lib/database.js:832:31)
  at PluginFlowEngineServer.runSQLByDataSourceKey (node_modules/@nocobase/plugin-flow-engine/dist/server/plugin.js:77:24)
  at flowSql:run (node_modules/@nocobase/plugin-flow-engine/dist/server/plugin.js:225:31)
2

the problem isn’t casting, it’s that the fechaini_prog column is type bigInt, and thus you cannot compare a date with it. maybe you can convert the date to unix timestamp first fechaini_prog >= EXTRACT(EPOCH FROM {{ 'desde' }})::BIGINT

3

image