DapperLib / DapperLib/Dapper

Error compile sql query "IN CLAUSE" to postgresql

Open
#817 6 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

db:postgresql
Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

create table foo (id varchar(25))

var foos = dbGerenciador.Conexao.Query<Foo>("SELECT * FROM foo WHERE id IN :ids ", new { ids = new string[] { "bar", "acme", "xpto" } });
{"ERROR: 42883: operator does not exist: character varying = text[]"}
    BaseMessage: "operator does not exist: character varying = text[]"
    Code: "42883"
    Data: {System.Collections.ListDictionaryInternal}
    Detail: ""
    ErrorCode: -2147467259
    ErrorSql: "SELECT * FROM foo WHERE id IN ((array[E'bar',E'acme',E'xpto'])::text[])"
    Errors: Count = 1
    File: "parse_oper.c"
    HResult: -2147467259
    HelpLink: null
    Hint: "No operator matches the given name and argument type(s). You might need to add explicit type casts."
    InnerException: null
    Line: "726"
    Message: "ERROR: 42883: operator does not exist: character varying = text[]"
    Position: "28"
    Routine: "op_error"
    Severity: "ERROR"
    Source: "Npgsql"
    StackTrace: "   em Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()\r\n   em Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()\r\n   em Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()\r\n   em Npgsql.ForwardsOnlyDataReader.NextResult()\r\n   em Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError)\r\n   em Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)\r\n   em Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)\r\n   em Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   em System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   em Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior)\r\n   em Dapper.SqlMapper.<QueryImpl>d__69`1.MoveNext()\r\n   em System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   em System.Linq
.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   em Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)\r\n   Sandbox\\Program.cs:linha 33"
    TargetSite: {Boolean MoveNext()}
    Where: ""

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the query from Sandbox\Program.cs at line 33 and inspect the Dapper.SqlMapper.Query path together with the reported Npgsql error. Done means the PostgreSQL IN query with the string[] parameter executes without the character varying = text[] error.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, postgresql, sql
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.