#462

REST list filters with `=` or `!=` ignore text values that contain upper case

OpenRaphaelFakhriopened 20d ago

Affected Component

Core Services (Frontend UI/Backend API)

Describe the bug

TableQuery.Init maps every text field of a REST list endpoint to LOWER(column::text). TableQuery.DataFilter lowercases a string value only for like and not like (and for the items of an in list). With =, !=, <, <=, >= or >, the value goes to the database as sent, so it is compared against a lowercased column.

As a result:

  • {"field":"title","operator":"=","value":"Recon Example"} returns no flows, even when a flow has exactly that title.
  • {"field":"title","operator":"!=","value":"Recon Example"} returns every flow, including the one with that title.
  • The same value works with in ("value":["Recon Example"]), because in items are lowercased.

This affects every REST list endpoint that uses rdb.TableQuery text mappers (flows, tasks, subtasks, containers, logs and others), for example a model or model_provider_name value with upper-case letters.

Steps to Reproduce

  1. Create a flow titled Recon Example.
  2. Call GET /api/v1/flows/?page=1&pageSize=5&type=filter&filters[]={"field":"title","operator":"=","value":"Recon Example"} with an API token.
  3. The response has total: 0. With "operator":"in","value":["Recon Example"] the same flow is returned.

Expected behavior

Text filters compare case-insensitively for every operator, the same way like and in already do.

System Configuration

Current main (843dfeb).

Activity

1
XinwangTao20d ago

Confirmed the root cause: TableQuery.Init wraps text fields in LOWER(column::text), but DataFilter only lowercases values for like/not like/in, so =, !=, <, > etc. compare a raw value against a lowercased column. That also explains why in works while = does not. +1 on the fix in #463 to lowercase values for every operator — that restores consistent case-insensitive semantics across all text filters. One suggestion: the LOWER(column::text) projection can defeat index usage on large tables, so it's worth keeping an eye on query plans for flows/tasks endpoints after this lands, and maybe documenting the case-insensitive behavior explicitly in the API docs.