elsa-workflows / elsa-workflows/elsa-core

DAPPER with PostgreSQL: CreateAndRunInstanceAsync fails with "Npgsql.PostgresException (0x80004005): 42883: operator does not exist: boolean = integer"

Open
#6,747 0 comments 0 reactions 0 assignees View on GitHub
triaged
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.