0

I try to fetch an execution plan from Postgresql via Npgsql and Dapper.

And versions of used software.

  • .Net Framework 4.6.1
  • ASP.NET WebAPI 2
  • Postgresql 9.4
  • Npgsql v3.0.5
  • Dapper v1.42.0

Non-EXPLAIN query with IN-clause with List<int> can fetch rows.

ex: SELECT 1 FROM banana WHERE banana_id IN @BananaIdList

But with EXPLAIN query throws followed Npgsql.NpgsqlException 42601: syntax error at or near \"$1\".

ex: EXPLAIN SELECT 1 FROM banana WHERE banana_id IN @BananaIdList

Why EXPLAIN syntax with IN-clause causes NpgsqlException?

Can I fetch execution plan?

.

I wrote a simple program for reproduce.

Running on Console project and WebAPI2 throws same Exception.

public class BadQuery {

    public async Task Test() {
        const string Sql = "EXPLAIN SELECT 1 FROM banana WHERE banana_id IN @BananaIdList";

        var parameters = new DynamicParameters();
        parameters.Add("@BananaIdList", new List<int> {1, 2, 3});

        var conn = new NpgsqlConnection("Server=127.0.0.1; Port=******; Database=******; User Id=******; Password=******;");
        conn.Open();

        var results = await conn.QueryAsync<string>(Sql, parameters); // throws NpgsqlException
    }

}

I found a similar question, but this question uses Array[].

"WHERE x IN y" clause with dapper and postgresql throwing 42601: syntax error at or near \"$1\"

Thanks.

Community
  • 1
  • 1
Lyc
  • 121
  • 8
  • Fair question. I suspect that it is because it is trying to use array semantics rather than parameter expansion. Have you tried `where banana_id=any(:BananaIdList)` ? – Marc Gravell Mar 08 '16 at 11:13
  • I've tested it locally, and it seems to work the same (correctly) regardless of list or array - you *should* just have to change it to use the `any` syntax, as per the question you've linked to. Have you tried that? – Marc Gravell Mar 08 '16 at 11:20
  • Thanks for reply @marc-gravell. I misunderstood query `in any(@ThemeIdList)`. So after changing to `= any(@ThemeIdList)`, probrems are resolved. Then, I think your replied reason is true. – Lyc Mar 09 '16 at 01:16
  • And finally, I understood a specifications(now) that use Dapper with Npgsql. A non-EXPLAIN query can use `in @ParametersOfList`. But an EXPLAIN query can only use `= any(@ParametersOfList)`. I was mazed by these differences, and these are minor bugs I think. – Lyc Mar 09 '16 at 01:17
  • Ugh... sorry, I found another reasons of probrem. I wrote can use non-Explain query with `IN-clause with List`, but can not use it _too_. This is not a bug. – Lyc Mar 09 '16 at 07:05
  • Why misunderstood this, I forgot that I use Glimpse. And wrap `PgsqlConnection` by `GlimpseDbConnection`, and these program hiding on deep. Then, Non-EXPLAIN queries are executed by `GlimpseDbConnection`, and EXPLAIN queries are executed by `PgsqlConnection`. Now I found wrap `PgsqlConnection` by `GlimpseDbConnection`, it can use `IN-clause with List`, but can not use `=any(List)`. These differences confuse me. – Lyc Mar 09 '16 at 07:05

1 Answers1

3

I forgot that I use Glimpse.
And wrap PgsqlConnection by GlimpseDbConnection, these program hiding on deep.

I used two connections to avoid EXPLAIN queries being displayed on Glimpse,
Non-EXPLAIN queries are executed by GlimpseDbConnection,
and, EXPLAIN queries are executed by PgsqlConnection.

Now I found defference about below.

PgsqlConnection
- Can not : IN-clause with List<int>
- Can :=any(List<int>)

GlimpseDbConnection (Include PgsqlConnection)
- Can : IN-clause with List<int>
- Can not :=any(List<int>)

These differences confuse me.

So the answer is "MUST use =any(List<T>)".

Lyc
  • 121
  • 8