Window Functions

Ties Need a Tie-Breaker

Ambiguous row order fails closed.

highlighted = computed this step

Ties need a tie-breaker

If two rows have the same partition and order key, ROW_NUMBER would need an extra ordering fact.

ambiguous ties fail closed\text{ambiguous ties fail closed}

Reject hidden order

The request is rejected; ambiguous tie groups are 1; result rows are 0.

result rows=0\text{result rows}=0

Window-function examples are tiny finite table transforms; SQL dialects, NULL ordering, frames beyond prefix rows, optimizer behavior, performance, and product behavior are out of scope.

Ties need a tie-breaker: base rowsidregiondayamount1east152east273west144east235west26 partitionspartitionKeysourceRowsrowCount[east][0, 1, 3]3[west][2, 4]2 window rowssourceRowpartitionKeyorderKeytieBreakerKeyrowNumbernonenonenonenonenone ambiguous tiespartitionKeydeterministicKeysourceRows[east][2][1, 3] window factsfactvaluerowCount5partitionCount2partitionColumnsregionorderColumnsdaytieBreakerColumnsnonefunctionsrow_numberresultRowCount0rejectedyesrejectionReasonambiguous_order_tiesambiguousTieGroups1rowOrderpartition_then_orderframenone

Read the tied rows

The ambiguous ties table names the rows that need an explicit tie-breaker.

no hidden order\text{no hidden order}

Summary

The compiler refuses to invent a row order.

fail closed\text{fail closed}