Bug: Unknown function untuple

[harshitsaamu]

I am trying to run the setup with managed Clickhouse (24.8), while running migrations from packages/db/code-migrations/3-init-ch.ts while creating event_property_values_mv it throws error:

Error running migration 3-init-ch.ts:
Error: Unknown function untuple. Maybe you meant: ['tuple'].

Failed SQL Query:
  CREATE MATERIALIZED VIEW IF NOT EXISTS event_property_values_mv
  ENGINE = AggregatingMergeTree()
  ORDER BY (project_id, name, property_key, property_value)
  AS SELECT
          project_id,
          name,
          key_value.keys as property_key,
          key_value.values as property_value,
          created_at
        FROM (
          SELECT
            project_id,
            name,
            untuple(arrayJoin(properties)) as key_value,  -- ❌ THIS LINE FAILS
            max(created_at) as created_at
          FROM events
          GROUP BY project_id, name, key_value
        )
        WHERE property_value != ''
          AND property_key != ''
          AND property_key NOT IN ('__duration_from', '__properties_from')
        GROUP BY project_id, name, property_key, property_value, created_at

The workaround that worked for me is:

  CREATE MATERIALIZED VIEW IF NOT EXISTS event_property_values_mv
  ENGINE = AggregatingMergeTree()
  ORDER BY (project_id, name, property_key, property_value)
  AS SELECT
      project_id,
      name,
      property_key,
      property_value,
      max(created_at) as created_at
  FROM events
  ARRAY JOIN mapKeys(properties) as property_key, mapValues(properties) as
  property_value
  WHERE property_value != ''
      AND property_key != ''
      AND property_key NOT IN ('__duration_from', '__properties_from')
  GROUP BY project_id, name, property_key, property_value;

Probable reason for this breaking could be how properties is being stored in events which is of type Map(String, String), is this something known or I am missing out something here

[lindesvard]

Trying to iron out this issue with Clickhouse community slack, I have seen this once (happen last week for me the first time). Unsure whats going on since untuple actually is a function...

https://clickhousedb.slack.com/archives/CU478UEQZ/p1762890845933899?thread_ts=1762337913.169459&cid=CU478UEQZ

[lindesvard]

Closing this since its not planned by clickhouse (I think its the old analyzer). We have upgraded to clickhouse 25 so hoping this will not occur for others