Error when use in

[Genuineh]

Code

using var cnn = new ClickHouseConnection(settings.ToString());

await cnn.OpenAsync().ConfigureAwait(false);

var cmd = cnn.CreateCommand("SELECT * FROM cktest WHERE id IN ({ids})");
cmd.Parameters.AddWithValue("ids", new long[] { 1, 2 });

var res = await cmd.ExecuteReaderAsync().ConfigureAwait(false);

Error

Octonica.ClickHouseClient.Exceptions.ClickHouseServerException : DB::Exception: Table test._b518ecd58bd34345b613ae87297fa501 doesn't exist

[victor-sushko]

Hello, @Genuineh

The right side of IN operator may be either a set of constant expressions or a table (see IN Operators for details).

You may modify your query and pass the array of values as a table with a single column:

using var cnn = new ClickHouseConnection(settings.ToString());

await cnn.OpenAsync().ConfigureAwait(false);

var cmd = cnn.CreateCommand("SELECT * FROM cktest WHERE id IN ids");

var ids = new long[] { 1, 2 };
var tableProvider = new ClickHouseTableProvider("ids", ids.Length);
tableProvider.AddColumn(ids);
cmd.TableProviders.Add(tableProvider);

var res = await cmd.ExecuteReaderAsync().ConfigureAwait(false);

[Genuineh]

Hi, @victor-sushko
Thanks for your replay, it work right.

[Genuineh]

hi, @victor-sushko
I got error when i want to use dapper style sql like "in @ids"

code

await cnn.QueryAsync<object>("SELECT * FROM cktest WHERE id IN @ids", new { ids = new[] { 1L, 2L } });

error

Octonica.ClickHouseClient.Exceptions.ClickHouseServerException : DB::Exception: Types of column 1 in section IN don't match: Int64 on the left, Array(Int64) on the right

Whether not support array parameters with dapper style