metabase-clickhouse-driver icon indicating copy to clipboard operation
metabase-clickhouse-driver copied to clipboard

Add support for `:window-functions/offset` feature

Open slvrtrn opened this issue 1 year ago • 0 comments

See the driver changelog for 0.50.0.

The :window-functions/offset (offset window functions) Metabase feature is currently disabled, as the default Metabase implementation is incompatible with ClickHouse; the tests showed that the generated queries are not valid:

clojure.lang.ExceptionInfo: Error executing query: Code: 63. DB::Exception: Aggregate function with name 'LAG' does not exists. In scope SELECT source.created_at AS created_at, SUM(source.total) AS sum, LAG(SUM(source.total), 1) OVER (ORDER BY source.created_at ASC) AS offset FROM (SELECT toStartOfYear(test_data.orders.created_at) AS created_at, test_data.orders.total AS total FROM test_data.orders) AS source GROUP BY source.created_at ORDER BY source.created_at ASC LIMIT 3. (UNKNOWN_AGGREGATE_FUNCTION) (version 24.3.1.2672 (official build))
                            , server ClickHouseNode [uri=http://localhost:8123/default, options={use_server_time_zone_for_dates=true,use_no_proxy=false,product_name=metabase/1.4.1}]@-2141897951
    driver: :clickhouse
    params: nil
       sql: ["-- Metabase"
             "SELECT"
             "  `source`.`created_at` AS `created_at`,"
             "  SUM(`source`.`total`) AS `sum`,"
             "  LAG(SUM(`source`.`total`), 1) OVER ("
             "    ORDER BY"
             "      `source`.`created_at` ASC"
             "  ) AS `offset`"
             "FROM"
             "  ("
             ...]
      type: :invalid-query
     java.sql.SQLException: Code: 63. DB::Exception: Aggregate function with name 'LAG' does not exists. In scope SELECT source.created_at AS created_at, SUM(source.total) AS sum, LAG(SUM(source.total), 1) OVER (ORDER BY source.created_at ASC) AS offset FROM (SELECT toStartOfYear(test_data.orders.created_at) AS created_at, test_data.orders.total AS total FROM test_data.orders) AS source GROUP BY source.created_at ORDER BY source.created_at ASC LIMIT 3. (UNKNOWN_AGGREGATE_FUNCTION) (version 24.3.1.2672 (official build))

See also: https://clickhouse.com/docs/en/sql-reference/window-functions#standard-window-functions (the lag/lead part)

slvrtrn avatar Jun 11 '24 17:06 slvrtrn