SELECT ('Hello' AS a, 123 AS b) fails on newer Clickhouse versions

[hkrutzer]

A query where I do this:

|> select([x], %{
   event: selected_as(fragment("tuple(?, ?)", x.event_type, x.event_module), :event),
})

started failing.

On version 23.12.2.59:

iex(4)> ClickhouseRepo.query!("SELECT ('Hello' AS a, 123 AS b)")
[11:22:05.855] [debug] module=Ecto.Adapters.SQL function=log/4
QUERY OK db=1.6ms idle=1406.1ms
SELECT ('Hello' AS a, 123 AS b) []

%Ch.Result{
  command: :select,
  num_rows: 1,
  rows: [[{"Hello", 123}]],
  headers: [
    {"date", "Thu, 22 Aug 2024 09:22:05 GMT"},
    {"connection", "Keep-Alive"},
    {"content-type", "application/octet-stream"},
    {"transfer-encoding", "chunked"},
    {"x-clickhouse-query-id", "7bc4812b-22da-4c0b-b58d-e07276bbd586"},
    {"x-clickhouse-format", "RowBinaryWithNamesAndTypes"},
    {"keep-alive", "timeout=30"},
    {"x-clickhouse-summary",
     "{\"read_rows\":\"1\",\"read_bytes\":\"1\",\"written_rows\":\"0\",\"written_bytes\":\"0\",\"total_rows_to_read\":\"0\",\"result_rows\":\"0\",\"result_bytes\":\"0\",\"elapsed_ns\":\"731417\"}"}
  ],
  data: nil
}

On v24.8.1.2684:

iex(4)> ClickhouseRepo.query!("SELECT ('Hello' AS a, 123 AS b)")
[11:21:36.957] [debug] module=Ecto.Adapters.SQL function=log/4
QUERY ERROR db=1.6ms decode=2.2ms idle=1543.0ms
SELECT ('Hello' AS a, 123 AS b) []

** (ArgumentError) failed to decode "Tuple(a String, b UInt8)" as ClickHouse type (no function clause matching in Ch.Types.decode/3)
    (ch 0.2.6) lib/ch/types.ex:338: Ch.Types.decode([:type, :close, {:tuple, [:type]}, []], "a String, b UInt8)", [])
    (ch 0.2.6) lib/ch/types.ex:328: Ch.Types.decode/1
    (ch 0.2.6) lib/ch/row_binary.ex:600: Ch.RowBinary.decode_types/1
    (ch 0.2.6) lib/ch/row_binary.ex:590: Ch.RowBinary.decode_types/3
    (ch 0.2.6) lib/ch/query.ex:183: DBConnection.Query.Ch.Query.decode/3
    (db_connection 2.7.0) lib/db_connection.ex:1479: DBConnection.decode/4
    (db_connection 2.7.0) lib/db_connection.ex:830: DBConnection.execute/4
    (ch 0.2.6) lib/ch.ex:87: Ch.query/4
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:519: Ecto.Adapters.SQL.query!/4
    iex:4: (file)

On the newer version, it does work when I disable the new feature:

ClickhouseRepo.query!("SELECT ('Hello' AS a, 123 AS b) SETTINGS enable_named_columns_in_function_tuple=0")

so it is very likely the new feature Function tuple will now try to construct named tuples in query (controlled by enable_named_columns_in_function_tuple) from Clickhouse 24.7.

[ruslandoga]

👋 @hkrutzer

Thank you for the report!

Right, Ch cannot parse named tuples right now. I'll try to prepare a fix later today.

[ruslandoga]

@hkrutzer would you be able to try out #197?

def deps do
  [
    # ...
    {:ch, github: "plausible/ch", branch: "parse-named-tuples", override: true},
    # ...
  ]
end

[hkrutzer]

Thanks @ruslandoga ! I can't immediately test it because it seems we can't upgrade to Ecto 3.12 yet.

[hkrutzer]

ClickhouseRepo.query!("SELECT ('a' as a, 'b' as b)") works now! But it seems an underscore is a problem: ClickhouseRepo.query!("SELECT ('a' as a_a, 'b' as b)") does not work.

Edit: ah I see you already wrote about that in the PR. Unfortunately, I have a column name with an underscore

[ruslandoga]

Thank you for trying it out!

I added $ and _ "support" in 0415d9f and released v0.2.8 :)

[hkrutzer]

Thank you!