Clickhouse: How to use Array with IN operator?

[yamrzou]

I have a column geohash with precision p used as a primary key. I want to leverage the index in queries of type :

SELECT ... WHERE geohash IN geohashesInBox(lon_min, lat_min, lon_max, lat_max, p)

The above query fails with :

Type mismatch in IN or VALUES section. Expected: String. Got: Array.

Becasue geohashesInBox returns Array(String).

The alternatives I tried :

• SELECT ... WHERE has(geohashesInBox(lon_min, lat_min, lon_max, lat_max, p), geostr)
  Slow and doesn't leverage the index.
• SELECT ... FROM some_table JOIN (SELECT arrayJoin(geohashesInBox(...)) AS box_geohash) ON geohash = box_geohash
  Faster, but the Join doe not use the index as mentioned here.

The ideal solution would be to use IN with a tuple returned by geohashesInBox.

Is there a way to convert the returned Array to a Tuple ? Something like the unpacking operator in Python : tuple(*some_array) ?

[filimonov]

Try that:

SELECT ... WHERE geohash IN (SELECT arrayJoin(geohashesInBox(...)) AS box_geohash)

Related: https://github.com/ClickHouse/ClickHouse/pull/5342

[Enmk]

Hi @yamrzou !
Well, there was a plan to fix operator IN just for that, but it has low priority due to a workaround @filimonov just posted.

And right now there is no way to convert Array to Tuple. IIRC, there are no plans to do that either.

[yamrzou]

Hi @Enmk,
For now the workaround proposed by @filimonov works perfectly. Thanks a lot !

[blinkov]

@yamrzou, do you have any further questions?

[yamrzou]

Thanks @blinkov. No, this issue can be closed.