Skip to content

SQL: ClickHouse dialect NULL ordering, division and countIf follow DataFusion #655

Open

Description

@zzylol

Found in the #539 review.

Problem

With SqlDialect::ClickhouseSQL, the SQL lowering follows DataFusion's semantics in three places where ClickHouse's differ, so the plan can return different rows or values from what ClickHouse would:

  • NULL ordering. ORDER BY x DESC lowers with nulls_first: true (DataFusion's default, nulls_max). ClickHouse puts NULLs last in both directions, so a top-k over a nullable column returns different rows.
  • Division. bytes / 2 on integers is typed Int64 and divides as integers (5 → 2). ClickHouse's / always returns Float64 (2.5).
  • countIf over no rows. It is rewritten to sum(CASE …), which is NULL over an empty input; ClickHouse returns 0.

The legacy SQL path has the same behaviour; #539 ported it unchanged.

Possible fix

Under ClickhouseSQL only:

  • set datafusion.sql_parser.default_null_ordering = "nulls_last";
  • cast both operands of / to Float64;
  • wrap the countIf rewrite in coalesce(…, 0).

Each needs a ClickHouse-dialect test.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions