Crash query with topK() on 25.3 Clickhouse version

[dbegunkov]

Hi! I have problem with use next query (for example):

from a in table_database.table_name,
        select: %{column: fragment("topK(?)(?.?)", ^limit, a, literal(^attribute.name))}

The error is

(Ch.Error) Code: 70. DB::Exception: Conversion from AggregateFunction(topK(50), Int32) to AggregateFunction(topK(50), Int32) is not supported: While executing Remote. (CANNOT_CONVERT_TYPE) (version 25.3.3.42 (official build))\n\n    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1\n    (ecto_ch 0.6.1) lib/ecto/adapters/clickhouse.ex:323: Ecto.Adapters.ClickHouse.execute/5\n    (ecto 3.12.5) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4\n    (ecto 3.12.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3...

With Clickhosuse version 24.3 and early this query worked sucessfull.

Important: the query without parameterized limit runs successful on all version Clickhouse, include 25.3.

from a in table_database.table_name,
        select: %{column: fragment("topK(50)(?.?)",  a, literal(^attribute.name))}

I think, the problem is that crashed query translates to follow sql

(select  topK({$0:Int64})(e0."field1")  FROM "table1" AS e0) [50]

and more specifically problem is in :Int64 type casting

[ruslandoga]

👋 @dbegunkov

Thank you for the report!

I tried running this query

Ch.query!(conn, "select topK({$0:Int64})(n.number) FROM numbers(1000) AS n", [10])

on clickhouse/clickhouse-server:latest-alpine and it seems to work.

PR: #253

The problem might be elsewhere. I'll need to take a closer look :)

[dbegunkov]

Thank you for quickly reply! Yes, this query don't work if was generate from ecto only.

[dbegunkov]

Although I reproduced the bug with raw sql query

 Ecto.Adapters.SQL.query!(conn, "SELECT topK({$0:Int64})(e0.\"cart_price\") FROM \"events_web_d\" AS e0 WHERE (e0.\"part_dt\" >= {$1:DateTime64(6)}) AND (e0.\"part_dt\" <= {$2:DateTime64(6)})", [50, ~N[2025-05-08 23:59:59.999999], ~N[2025-05-22 23:59:59.999999]])
** (Ch.Error) Code: 70. DB::Exception: Conversion from AggregateFunction(topK(50), Int32) to AggregateFunction(topK(50), Int32) is not supported: While executing Remote. (CANNOT_CONVERT_TYPE) (version 25.3.3.42 (official build))

    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
    iex:13: (file)
{"id":"49d02308-8c2d-4ae9-b9a7-f158c7962c61","ts":"2025-05-23T08:33:34.749050Z","level":"debug","msg":"QUERY ERROR db=495.1ms idle=1432.6ms\nSELECT topK({$0:Int64})(e0.\"cart_price\") FROM \"events_web_d\" AS e0 WHERE (e0.\"part_dt\" >= {$1:DateTime64(6)}) AND (e0.\"part_dt\" <= {$2:DateTime64(6)}) [50, ~N[2025-05-08 23:59:59.999999], ~N[2025-05-22 23:59:59.999999]]","err":"","code":"","trace":{},"context":"{\"application\":\"ecto_sql\"}","env":"n/a","application":{"name":"ems","version":"0.1.0","product":"event-management"},"request":{},"response":{}}

[dbegunkov]

Sorry, the table events_web_d for example:

CREATE TABLE events_web_d
(

    `part_dt` Date,

    `cart_price` Int32

)

[ruslandoga]

Thank you for the update! But the updated test in #253 still seems to pass ...

I'll try using the exact 25.3.3.42 ClickHouse version instead of latest.
UPDATE: the queries in that PR still seem to work with 25.3.3.42.

[ruslandoga]

While executing Remote.

Do you use Distributed tables by any chance? Maybe the issue is there somewhere.

[dbegunkov]

Yes, we using distributed tables.

[ruslandoga]

Would you be able to try and reproduce the issue in a "normal" table? I think the problem might be not in Ch but rather in how ClickHouse does aggregation of the intermediate results from different nodes for a parameterized topK function. In which case, the issue would need to be opened in https://github.com/ClickHouse/ClickHouse

[dbegunkov]

yes, this bug links with Distributed table, on normal tables the crash doesn't reproduced. The fail doesn't link with Ch lib, yes. Thank you for your help!