Npgsql.PostgresException: 42809: op ANY/ALL (array) requires array on right side

[babakarj]

When changing
IDbConnection connection = new NpgsqlConnection(connectionString);
to
IDbConnection connection = new StackExchange.Profiling.Data.ProfiledDbConnection(new NpgsqlConnection(connectionString), MiniProfiler.Current);

Queries containing Any() fails with following error:

Npgsql.PostgresException
  HResult=0x80004005
  Message=42809: op ANY/ALL (array) requires array on right side
  Source=Npgsql
  StackTrace:
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 205
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1064
   at Dapper.SqlMapper.<QueryImpl>d__138`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1081
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723
   ...

Sample query: SELECT * FROM public."tblcars" WHERE "Id"=ANY(@Ids);

private IDbConnection OpenConnection(string connectionString)
{
	IDbConnection connection = new StackExchange.Profiling.Data.ProfiledDbConnection(new NpgsqlConnection(connectionString), MiniProfiler.Current);
	// IDbConnection connection = new NpgsqlConnection(connectionString);
	connection.Open();
	return connection;
}

public IEnumerable<Car> Get(int[] ids)
{
	string query = $"SELECT * FROM public.\"tblCar\" WHERE \"Id\"=ANY(@Ids);";

	using (IDbConnection connection = OpenConnection(this.ConnectionString))
	{
		return connection.Query<Car>(query, new { Ids = ids }).ToList();
	}
}

Any idea?

[roji]

There isn't enough info provided - you need to post the code that creates the command and assigns the parameter (@Ids).

[babakarj]

@roji it's updated.

[NickCraver]

Perhaps something in the provider is not friendly to a generic IDbConnection underneath when it comes to arrays?

@roji any idea with the updated info what might be going on?

[roji]

Oh, you're using Dapper - this is likely the reason the query is failing. Dapper has its own facility for interpreting arrays, executing the command multiple items on each element instead of executing it once with an array element.

The following ADO.NET code (which doesn't use dapper) works just fine:

using (var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM data WHERE id = ANY(@ids)", conn))
{
    cmd.Parameters.AddWithValue("@ids", new[] { 8, 9 });
    Console.WriteLine(cmd.ExecuteScalar());
}

I'm not sure if Dapper has some way to make it disable the array-to-multiple-command execution which would allow this to work, but you can always drop down to ADO.NET for this particular query...

[NickCraver]

Going to close this out on the MiniProfiler side since the issue is with the actual command...MiniProfiler is just in the stack on the way there (and this problem exists without it). If problems persist though, let's chase this down in the Dapper repo.

[moxplod]

Hi - i just ran into this issue and found this online. I think the issue is with MiniProfiler, as it works without miniprofiler and as soon as i add miniprofiler to the connection it fails at that request. Both use the same underlying dapper and npgsql connection.

// does not work ->
//return new StackExchange.Profiling.Data.ProfiledDbConnection(connection, MiniProfiler.Current);

// works ->
return connection;

[sep2]

The problem is still there, should this issue be reopoened?

   // can work
    member this.CreateConnection() =
        new NpgsqlConnection(settings.ConnectionString)

   // cannot work
    member this.CreateConnection() =
        let connection = new NpgsqlConnection(settings.ConnectionString)
        new StackExchange.Profiling.Data.ProfiledDbConnection(connection, MiniProfiler.Current)