opt: push filters into window functions when columns are referenced in the ORDER BY

[justinj]

We already push down filters that are determined by the partition-by columns of the window function. We should also be able to push down filters bound by the order-by in certain situations.

Consider this query:

SELECT acct-id, time, ravg
FROM (SELECT  acct-id, time,
     AVG(balance) OVER (PARTITION BY acc-id ORDER BY
       time ROWS BETWEEN UNBOUNDED
      PROCEEDING
         AND CURRENT ROW) ravg
      FROM accounts
      WHERE acct-id = ’ORCL’ AND
time<=12);

The filter on time can be pushed down, since the window function being computed is prefix-safe and the filter allows through a prefix of the results.

Jira issue: CRDB-5615

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!