Skip to content

Invalid DISTINCT + ORDER BY behaviour description #2985

Open
@fider

Description

@fider

Database versions invalid behavior observed:

DESCRIBTION:

Docs https://clickhouse.com/docs/en/sql-reference/statements/select/distinct#distinct-and-order-by tells that:

  • "The DISTINCT clause is executed before the ORDER BY clause."

Problem:

  • ORDER BY is executed before DISTINCT and that affects business logic that query executes.

Prove:

  • Execute below query taken from docs
SELECT distinct a
FROM (
    SELECT 2 AS a, 1 AS b UNION ALL
    SELECT 1 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 4 AS b
) AS simulated_table_with_data
ORDER BY b desc

Expected results due to docs should be 3, 1, 2 but it is 2, 3, 1.
Above query gives me this results in both mentioned versions of database, additionally it's proved by other query in my current project and CH version 23.9.2.56.

Btw.
it's nice that docs are actually wrong - it makes this feature more useful.

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions