Positional Arguments in ClickHouse SQL

[JackFielding]

I am using ibis with ClickHouse. I work with a few on-prem ClickHouse deployments with various ClickHouse versions, all older than
21.10.

The ClickHouse SQL ibis generates always seems to use positional arguments in the GROUP BY, e.g. GROUP BY 1, 2.

In ClickHouse 21.10 the ability to use positional arguments was added with the enable_positional_arguments setting. Before this version it was impossible. In all version named GROUP BY arguments are supported.

Is there a reason ibis uses positional arguments rather than named arguments? Using named arguments would enable the use of ibis with older ClickHouse versions.

import ibis
connection = ibis.clickhouse.connect()
t = connection.table("hello2")
query = t.group_by("aString").aggregate(t["anInt"].sum()).order_by(["aString"])
ibis.show_sql(query)

prints

SELECT
  *
FROM (
  SELECT
    t0.aString,
    SUM(t0.anInt)
  FROM default.hello2 AS t0
  GROUP BY
    1                                            # if this was t0.aString older version of ClickHouse would be happy
) AS t1
ORDER BY
  t1.aString

[JackFielding]

I tried digging in the code and make a little hack that "seems" on a tiny test case to do what I want.

Changing two lines here from

    if by:
        sel = sel.group_by(*map(str, range(1, len(by) + 1)), dialect="clickhouse")

to

if group_bys := op.by:
    sel = sel.group_by(*map(tr_val_no_alias, group_bys), dialect="clickhouse")

seems to create SQL that an older version (as well as all new versions ) of ClickHouse would like.

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")

prints

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

[JackFielding]

opened feature request: #6101

[cpcloud]

This was addressed in #6106.