bug: Do not use Positional Arguments in ClickHouse GROUP BY

[JackFielding]

Is your feature request related to a problem?

It would be preferable if the generated ClickHouse SQL did not use positional arguments for GROUP BY, e.g. GROUP BY 1, 2 and used the columns names instead, e.g. GROUP BY col_1, col_2.

Using positional arguments rather than named arguments has the following inconveniences:

• The generated SQL queries are invalid for ClickHouse versions prior to 21.10. I regularly use old versions like this.
• For ClickHouse versions from 21.10 but before 22.7 the user must explicitly specify the enable_positional_arguments setting otherwise the queries are invalid. e.g. pass settings={"enable_positional_arguments": True} to the backend.

Using named rather than positional arguments would be valid in all ClickHouse versions.

In the discussion: #6099 I had a go at hacking the code and found what appeared to be a solution.

Describe the solution you'd like

As per: #6099. Using the code:

import ibis

t = ibis.table(dict(a="int", b="str", c="str"), name="t")
expr = t.group_by(["b", "c"]).agg(t["a"].sum().name("hi"))
ibis.show_sql(expr, dialect="clickhouse")

instead of generating the query:

SELECT
  t0.b,
  t0.c,
  SUM(t0.a) AS hi
FROM t AS t0
GROUP BY
  1,
  2

it would be preferable if ibis generated the query:

SELECT
  t0.b,
  t0.c,
  SUM(t0.a) AS hi
FROM t AS t0
GROUP BY
  t0.b,
  t0.c

What version of ibis are you running?

5.0.1

What backend(s) are you using, if any?

ClickHouse

Code of Conduct

• I agree to follow this project's Code of Conduct