elsa-workflows / elsa-workflows/elsa-core
DAPPER with PostgreSQL: CreateAndRunInstanceAsync fails with "Npgsql.PostgresException (0x80004005): 42883: operator does not exist: boolean = integer"
- Dominant language
- C#
- Stars
- 7.9k
- Forks
- 1.5k
- Avg merge
- 15h 22m
- Merged PRs (30d)
- 114
Description
## Description
CreateAndRunInstanceAsync fails with "Npgsql.PostgresException (0x80004005): 42883: operator does not exist: boolean = integer"
## Steps to Reproduce
1. Use Dapper Migrations for a postgres Database
2. Attempt to run a workflow using workflowClient.CreateAndRunInstanceAsync. workflowClient is a local workflowClient. Use VersionOptions.Published for the WorkflowDefinitionHandle as this is part of the cause it seems.
Example Code:
```c#
var workflowClient = await _workflowRuntime.CreateClientAsync();
var instanceResponse = await workflowClient.CreateAndRunInstanceAsync(
new CreateAndRunWorkflowInstanceRequest()
{
WorkflowDefinitionHandle =
WorkflowDefinitionHandle.ByDefinitionId(
activeChannel.WorkflowDefinitionID, //HelloWorld.DefinitionId
VersionOptions.Published),
CorrelationId = correlationId,
Input = new Dictionary
{
{ "OptionsVariable", swOptions },
{ nameof(ContactAction), ca }
}
}
);
```
**Reproduction Rate**:
Always
## Environment
- **Elsa Package Version**:
- Elsa: Version 3.4.0
- Elsa.Dapper: Version 3.4.0
- Elsa.Dapper.Migrations: Version 3.4.0
- **Operating System**: Mint 22.1
- **Browser and Version**: NA
## Troubleshooting Attempts
This seems to be due to this code in Elsa.Dapper.Extensions.ParameterizedQueryBuilderExtensions
The below is called when creating the sql to run to lookup the Workflow Defination.
```c#
public static ParameterizedQuery Is(this ParameterizedQuery query, VersionOptions? versionOptions)
{
if (versionOptions == null) return query;
var sql = query.Sql;
var options = versionOptions.Value;
if (options.IsDraft) sql.AppendLine("and IsPublished = 0");
if (options.IsLatest) sql.AppendLine("and IsLatest = 1");
if (options.IsPublished) sql.AppendLine("and IsPublished = 1");
if (options.IsLatestOrPublished) sql.AppendLine("and (IsLatest = 1 or IsPublished = 1)");
if (options.IsLatestAndPublished) sql.AppendLine("and IsLatest = 1 and IsPublished = 1");
if (options.Version > 0)
{
sql.AppendLine(query.Dialect.And("Version"));
query.Parameters.Add("@Version", options.Version);
}
return query;
}
```
When VersionOptions.Published is set to true, It generates SQL with IsPublished = 1. Dapper Migrations creates the table WorkflowDefinitions with the column IsPublished as bool.
The generated SQL is :
```
select * from WorkflowDefinitions where 1=1
and TenantId is null
and DefinitionId = $1
and IsPublished = 1
```
The column is bool and its querying against an int value.
PostgreSQL seems to require an explicit cast to check for equality between a boolean and integer value and generates the above error.
CreateAndRunInstanceAsync works as expected when using EFCore as the persistence .
Contributor guide
Research direction
Inspect Elsa.Dapper.Extensions.ParameterizedQueryBuilderExtensions, especially the Is method that builds the VersionOptions.Published query. Reproduce CreateAndRunInstanceAsync with PostgreSQL and confirm the generated predicate compares the boolean IsPublished column correctly; done means the workflow lookup succeeds without the 42883 error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 45/100