SQLite (SQL generally?) visibility problems with custom text fields

[dhiaayachi]

Expected Behavior

Searching by a custom text field should work, as it does using the ES backend.

Actual Behavior

After upserting the custom attribute from a workflow with a value like configdriven-stresstest, subsequent queries fail with errors like the following:

"level":"error","ts":"2023-08-03T16:49:59.640-0700","msg":"service failures","operation":"CountWorkflowExecutions","wf-namespace":"default","error":"CountWorkflowExecutions operation failed. Query failed: SQL logic error: no such column: stresstest (1)

or

error":"ListWorkflowExecutions operation failed. Select failed: SQL logic error: no such column: stresstest (1)",

My guess is there is some kind of escaping or parsing issue with dashes going on here.

Steps to Reproduce the Problem

1. temporal operator search-attribute create --name CustomStringField --type Text
2. Upsert the value from a workflow with something with dashes in it like foo-bar
3. Attempt to query/list for WFs matching that value

Specifications

• Version: 1.21.4
• Platform: Temporal CLI using SQLite backend

[dhiaayachi]

Thanks for reporting this issue.

It appears that the issue you're experiencing is related to how Temporal handles special characters like dashes (-) in custom text fields. You're correct that dashes can cause problems, and Temporal's SQLite backend does not yet support them in custom text fields.

It's best to avoid dashes in your custom search attribute values. Consider using underscores (_) instead for now, as it is currently supported.

We understand this limitation and are working on improving the support for special characters in future releases.

In the meantime, you can find more information about using search attributes in the Observability section of our documentation.

[dhiaayachi]

Thanks for reporting this issue. It looks like you're running into an issue where custom text fields with dashes are not being parsed correctly when querying workflow executions using the SQLite backend.

This appears to be a known issue and we are currently working on a fix. You can find more information about this issue and its potential solutions in our documentation.

In the meantime, you can try the following workaround:

1. Use underscores instead of dashes: Replace the dashes in your custom field values with underscores. For example, instead of configdriven-stresstest, use configdriven_stresstest.
2. Use a different backend: If possible, consider using a different backend such as Elasticsearch or MySQL for your Visibility store.

We apologize for any inconvenience this issue may cause. We appreciate your patience as we work on a resolution.