jayzhan211 commented on code in PR #27439:
URL: https://github.com/apache/datafusion/pull/27439#discussion_r4600077656


##########
docs/source/library-user-guide/upgrading/57.0.0.md:
##########
@@ -39,6 +39,16 @@ parents and siblings unchanged. The values themselves are 
shared through `Arc`.
 Update logical-plan snapshots if needed: generated lambda parameter names are
 local to lexical scopes, so sibling lambdas may reuse names.
 
+### NULL results of set-monotonic aggregate UDFs
+
+The NULL behavior assumed by window ordering inference is now documented
+explicitly in `SetMonotonicity`.

Review Comment:
   Optional cleanup: a UDAF author reading this can't tell what to check 
without opening the rustdoc, so stating the rule here makes the entry 
actionable.
   
   ```suggestion
   A UDAF that returns `SetMonotonicity::Increasing` or `Decreasing` must 
return a
   non-NULL result once it has seen a non-NULL input, and must not go back to 
NULL as
   more inputs are added. Window ordering inference relies on this; see 
`SetMonotonicity`.
   ```



##########
datafusion/sqllogictest/test_files/window_ordering.slt:
##########
@@ -665,6 +659,102 @@ physical_plan
 04)------SortExec: expr=[x@0 DESC], preserve_partitioning=[false]
 05)--------DataSourceExec: partitions=1, partition_sizes=[1]
 
+# REGRESSION-COUNT-DISTINCT: Ordered non-NULL input does not make a sliding 
distinct count monotonic
+query I
+SELECT COUNT(DISTINCT x) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT 
ROW) AS c
+FROM (VALUES (1), (1), (2), (3), (3)) AS t(x)
+ORDER BY c ASC;
+----
+1
+1
+2
+2
+3
+
+query TT
+EXPLAIN SELECT COUNT(DISTINCT x) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 
CURRENT ROW) AS c
+FROM (VALUES (1), (1), (2), (3), (3)) AS t(x)
+ORDER BY c ASC;
+----
+physical_plan
+01)SortExec: expr=[c@0 ASC NULLS LAST], preserve_partitioning=[false]
+02)--ProjectionExec: expr=[count(DISTINCT t.x) ORDER BY [t.x ASC NULLS LAST] 
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW@1 as c]
+03)----BoundedWindowAggExec: wdw=[count(DISTINCT t.x) ORDER BY [t.x ASC NULLS 
LAST] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: Field { "count(DISTINCT t.x) 
ORDER BY [t.x ASC NULLS LAST] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW": Int64 
}, frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW], mode=[Sorted]
+04)------ProjectionExec: expr=[column1@0 as x]
+05)--------SortExec: expr=[column1@0 ASC NULLS LAST], 
preserve_partitioning=[false]
+06)----------DataSourceExec: partitions=1, partition_sizes=[1]
+
+# REGRESSION-FILTER: A sliding FILTER can produce NULLs between non-NULL 
results
+query I
+SELECT MAX(x) FILTER (WHERE x % 2 = 0) OVER (
+    ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+) AS m FROM v ORDER BY m ASC NULLS LAST;
+----
+2
+4
+NULL
+NULL
+NULL
+
+query TT
+EXPLAIN SELECT MAX(x) FILTER (WHERE x % 2 = 0) OVER (
+    ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+) AS m FROM v ORDER BY m ASC NULLS LAST;
+----
+physical_plan
+01)SortExec: expr=[m@0 ASC NULLS LAST], preserve_partitioning=[false]
+02)--ProjectionExec: expr=[max(v.x) FILTER (WHERE v.x % Int64(2) = Int64(0)) 
ORDER BY [v.x ASC NULLS LAST] ROWS BETWEEN CURRENT ROW AND CURRENT ROW@1 as m]
+03)----BoundedWindowAggExec: wdw=[max(v.x) FILTER (WHERE v.x % Int64(2) = 
Int64(0)) ORDER BY [v.x ASC NULLS LAST] ROWS BETWEEN CURRENT ROW AND CURRENT 
ROW: Field { "max(v.x) FILTER (WHERE v.x % Int64(2) = Int64(0)) ORDER BY [v.x 
ASC NULLS LAST] ROWS BETWEEN CURRENT ROW AND CURRENT ROW": nullable Int32 }, 
frame: ROWS BETWEEN CURRENT ROW AND CURRENT ROW], mode=[Sorted]
+04)------SortExec: expr=[x@0 ASC NULLS LAST], preserve_partitioning=[false]
+05)--------DataSourceExec: partitions=1, partition_sizes=[1]
+
+# CONTROL-FULL-PARTITION-ROW-NUMBER: A whole-partition frame does not make 
ROW_NUMBER constant
+query I
+SELECT ROW_NUMBER() OVER (
+    ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+) AS r FROM v ORDER BY r DESC;
+----
+5
+4
+3
+2
+1
+
+query TT
+EXPLAIN SELECT ROW_NUMBER() OVER (
+    ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+) AS r FROM v ORDER BY r DESC;
+----
+physical_plan
+01)SortExec: expr=[r@0 DESC], preserve_partitioning=[false]
+02)--ProjectionExec: expr=[row_number() ORDER BY [v.x ASC NULLS LAST] ROWS 
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING@1 as r]
+03)----BoundedWindowAggExec: wdw=[row_number() ORDER BY [v.x ASC NULLS LAST] 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: Field { "row_number() 
ORDER BY [v.x ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 
FOLLOWING": UInt64 }, frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 
FOLLOWING], mode=[Sorted]
+04)------SortExec: expr=[x@0 ASC NULLS LAST], preserve_partitioning=[false]
+05)--------DataSourceExec: partitions=1, partition_sizes=[1]
+
+# REGRESSION-GROWING-EXTREMUM-NULLS: Opposite value directions do not make a 
leading NULL constant
+query I
+SELECT MAX(x) OVER (
+    ORDER BY x DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+) AS m FROM nullable ORDER BY m ASC NULLS LAST;
+----
+5
+5
+5
+NULL
+
+query TT
+EXPLAIN SELECT MAX(x) OVER (
+    ORDER BY x DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+) AS m FROM nullable ORDER BY m ASC NULLS LAST;
+----
+physical_plan
+01)SortExec: expr=[m@0 ASC NULLS LAST], preserve_partitioning=[false]
+02)--ProjectionExec: expr=[max(nullable.x) ORDER BY [nullable.x DESC NULLS 
FIRST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW@1 as m]
+03)----BoundedWindowAggExec: wdw=[max(nullable.x) ORDER BY [nullable.x DESC 
NULLS FIRST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { 
"max(nullable.x) ORDER BY [nullable.x DESC NULLS FIRST] ROWS BETWEEN UNBOUNDED 
PRECEDING AND CURRENT ROW": nullable Int32 }, frame: ROWS BETWEEN UNBOUNDED 
PRECEDING AND CURRENT ROW], mode=[Sorted]
+04)------SortExec: expr=[x@0 DESC], preserve_partitioning=[false]
+05)--------DataSourceExec: partitions=1, partition_sizes=[1]

Review Comment:
   @blaginin thanks for this! The `continue` for PARTITION BY keys that aren't 
sorted (`windows/mod.rs:387`) has no test. If I delete it, all slt files still 
pass, but `ORDER BY c LIMIT 4` below returns `1,1,2,3` instead of being 
rejected, because COUNT restarts in each of the interleaved partitions. Only 
unbounded input reaches this (bounded input always gets `Sorted` mode). Could 
you add this test? I ran it: it passes on this branch and fails with the gate 
removed.
   
   ````suggestion
   05)--------DataSourceExec: partitions=1, partition_sizes=[1]
   
   statement ok
   CREATE UNBOUNDED EXTERNAL TABLE unsorted_partition(a0 INT, a INT, b INT, c 
INT, d INT) STORED AS CSV WITH ORDER (a ASC, b ASC, c ASC) LOCATION 
'../core/tests/data/window_2.csv' OPTIONS ('format.has_header' 'true');
   
   # REGRESSION-UNSORTED-PARTITION: With an unsorted PARTITION BY key (Linear 
mode), COUNT resets
   # between interleaved partitions, so the final sort must stay and the 
unbounded query is rejected.
   query error Cannot execute pipeline breaking queries, operator: SortExec: 
TopK
   SELECT COUNT(*) OVER (PARTITION BY d ORDER BY a ROWS UNBOUNDED PRECEDING) AS 
c FROM unsorted_partition ORDER BY c LIMIT 4;
   ````



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to