clickhouse-docs icon indicating copy to clipboard operation
clickhouse-docs copied to clipboard

Invalid DISTINCT + ORDER BY behaviour description

Open fider opened this issue 11 months ago • 0 comments

Database versions invalid behavior observed:

  • 23.9.2.56 (used in my project)
  • 24.10.1.10895 (playground - https://sql.clickhouse.com/)

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.

fider avatar Dec 17 '24 14:12 fider