Implementing windows fuzzer k-range frames

[minhancao]

Implementing this enhancement of WindowsFuzzer being able to generate k-range frames - https://github.com/facebookincubator/velox/issues/9572

Implementation is based off of WindowTestBase.cpp.

Ran the following command to run window fuzzer test:

_build/debug/velox/functions/prestosql/fuzzer/velox_window_fuzzer_test --enable_window_reference_verification --presto_url="http://127.0.0.1:8080" --req_timeout_ms=2000 --duration_sec=60 --logtostderr=1 --minloglevel=0 > window_fuzzer_test_range_frame_4.out 2>&1

I20240425 11:34:11.399708 6542153 AggregationFuzzerBase.cpp:402] Executing query plan: 
-- Window[partition by [p0, p1] order by [s0 ASC NULLS LAST] w0 := covar_pop(ROW["c0"],ROW["c1"]) RANGE between 6 PRECEDING and 7 PRECEDING] -> c0:DOUBLE, c1:DOUBLE, s0:ROW<f0:INTERVAL DAY TO SECOND>, p0:ARRAY<DOUBLE>, p1:INTERVAL DAY TO SECOND, row_number:BIGINT, w0:DOUBLE
  -- Values[1000 rows in 10 vectors] -> c0:DOUBLE, c1:DOUBLE, s0:ROW<f0:INTERVAL DAY TO SECOND>, p0:ARRAY<DOUBLE>, p1:INTERVAL DAY TO SECOND, row_number:BIGINT

I20240425 11:34:51.405833 6542153 WindowFuzzer.cpp:265] ==============================> Done with iteration 77
I20240425 11:34:51.413125 6542153 AggregationFuzzerBase.cpp:654] Total functions tested: 27
I20240425 11:34:51.413216 6542153 AggregationFuzzerBase.cpp:655] Total iterations requiring sorted inputs: 64 (82.05%)
I20240425 11:34:51.413239 6542153 AggregationFuzzerBase.cpp:657] Total iterations verified against reference DB: 12 (15.38%)
I20240425 11:34:51.413255 6542153 AggregationFuzzerBase.cpp:659] Total functions not verified (verification skipped / not supported by reference DB / reference DB failed): 31 (39.74%) / 21 (26.92%) / 0 (0.00%)
I20240425 11:34:51.413271 6542153 AggregationFuzzerBase.cpp:664] Total failed functions: 27 (34.62%)
I20240425 11:34:51.413286 6542153 WindowFuzzer.cpp:479] Total functions verified in reference DB: 10
[==========] Running 0 tests from 0 test suites.
[==========] 0 tests from 0 test suites ran. (0 ms total)
[  PASSED  ] 0 tests.

[netlify[bot]]

✅ Deploy Preview for meta-velox canceled.

Name	Link
🔨 Latest commit	82706cf37a6e444bd243005a4fa9cc867191a10d
🔍 Latest deploy log	https://app.netlify.com/sites/meta-velox/deploys/6670abe9b6efbb0008084bfc

[aditi-pandit]

@minhancao : Please follow https://facebookincubator.github.io/velox/develop/window.html#k-range-frames on how to setup k range frames for Window PlanNode.

https://github.com/facebookincubator/velox/blob/main/velox/functions/lib/window/tests/WindowTestBase.cpp#L195 shows related tests code.

[minhancao]

@pramodsatya Ran the fuzzer for 1hr, it was able to run without any failures to stop it

I20240507 15:57:20.689369 14876119 WindowFuzzer.cpp:225] ==============================> Started iteration 6844 (seed: 2430860766)
I20240507 15:57:27.666707 14876119 AggregationFuzzerBase.cpp:434] Executing query plan: 
-- Window[partition by [p0, p1] order by [row_number ASC NULLS LAST] w0 := set_union(ROW["c0"]) RANGE between k0 PRECEDING and k1 FOLLOWING] -> c0:ARRAY<ARRAY<VARBINARY>>, p0:VARBINARY, p1:ROW<f0:INTERVAL DAY TO SECOND,f1:DATE,f2:DOUBLE,f3:BIGINT,f4:DOUBLE,f5:VARBINARY>, row_number:BIGINT, k0:BIGINT, k1:BIGINT, w0:ARRAY<ARRAY<VARBINARY>>
  -- Values[1000 rows in 10 vectors] -> c0:ARRAY<ARRAY<VARBINARY>>, p0:VARBINARY, p1:ROW<f0:INTERVAL DAY TO SECOND,f1:DATE,f2:DOUBLE,f3:BIGINT,f4:DOUBLE,f5:VARBINARY>, row_number:BIGINT, k0:BIGINT, k1:BIGINT
I20240507 15:57:28.619444 15060369 Task.cpp:1127] All drivers (1) finished for task test_cursor 6845 after running for 951 ms.
I20240507 15:57:28.619511 15060369 Task.cpp:1863] Terminating task test_cursor 6845 with state Finished after running for 951 ms.
I20240507 15:57:28.709437 14876119 AggregationFuzzerBase.cpp:515] Query not supported by the reference DB
I20240507 15:57:28.728335 14876119 WindowFuzzer.cpp:293] ==============================> Done with iteration 6844

I20240507 15:57:28.728335 14876119 WindowFuzzer.cpp:293] ==============================> Done with iteration 6844
I20240507 15:57:28.746685 14876119 AggregationFuzzerBase.cpp:687] Total functions tested: 60
I20240507 15:57:28.746722 14876119 AggregationFuzzerBase.cpp:688] Total iterations requiring sorted inputs: 4180 (61.07%)
I20240507 15:57:28.746743 14876119 AggregationFuzzerBase.cpp:690] Total iterations verified against reference DB: 1448 (21.15%)
I20240507 15:57:28.746759 14876119 AggregationFuzzerBase.cpp:692] Total functions not verified (verification skipped / not supported by reference DB / reference DB failed): 2894 (42.28%) / 1519 (22.19%) / 196 (2.86%)
I20240507 15:57:28.746776 14876119 AggregationFuzzerBase.cpp:697] Total failed functions: 1460 (21.33%)
I20240507 15:57:28.746791 14876119 WindowFuzzer.cpp:503] Total functions verified in reference DB: 50
[==========] Running 0 tests from 0 test suites.
[==========] 0 tests from 0 test suites ran. (0 ms total)
[  PASSED  ] 0 tests.

[minhancao]

Ran fuzzer with Aditi's changes for repetition of partition rows:

I20240508 14:34:15.494796 15514295 WindowFuzzer.cpp:379] ==============================> Done with iteration 7221
I20240508 14:34:15.495971 15514295 AggregationFuzzerBase.cpp:687] Total functions tested: 60
I20240508 14:34:15.496001 15514295 AggregationFuzzerBase.cpp:688] Total iterations requiring sorted inputs: 4325 (59.89%)
I20240508 14:34:15.496021 15514295 AggregationFuzzerBase.cpp:690] Total iterations verified against reference DB: 1562 (21.63%)
I20240508 14:34:15.496039 15514295 AggregationFuzzerBase.cpp:692] Total functions not verified (verification skipped / not supported by reference DB / reference DB failed): 3092 (42.81%) / 1553 (21.50%) / 198 (2.74%)
I20240508 14:34:15.496057 15514295 AggregationFuzzerBase.cpp:697] Total failed functions: 1592 (22.04%)
I20240508 14:34:15.496073 15514295 WindowFuzzer.cpp:589] Total functions verified in reference DB: 49
[==========] Running 0 tests from 0 test suites.
[==========] 0 tests from 0 test suites ran. (0 ms total)
[  PASSED  ] 0 tests.

[minhancao]

Hi @kagamiori! Thank you for your review comments, I have addressed them accordingly in my commit. Yes I have tried running the velox_window_fuzzer_test with PrestoQueryRunner. @pramodsatya and I are currently working on some code changes to pass the frame clause correctly from Velox to Presto in this PR: https://github.com/facebookincubator/velox/pull/10006. The PR's branch is created based on the branch on this PR. We are also working on a design doc for our approach and will ping you when it's ready to be reviewed!

[kagamiori]

Hi @kagamiori! Thank you for your review comments, I have addressed them accordingly in my commit. Yes I have tried running the velox_window_fuzzer_test with PrestoQueryRunner. @pramodsatya and I are currently working on some code changes to pass the frame clause correctly from Velox to Presto in this PR: #10006. The PR's branch is created based on the branch on this PR. We are also working on a design doc for our approach and will ping you when it's ready to be reviewed!

Hi @minhancao, that sounds great! Thank you for sharing the plan and helping extend the window fuzzer.

[kagamiori]

Hi @minhancao and @pramodsatya, how is the development going? I wonder whether there is an ETA of the mini-design about how PrestoQueryRunner gets the original SQL query from the Velox query plan with k-range frames. Please let me know if you need code reviews or if there is something I could help. I'm happy to chat.

[pramodsatya]

Hi @minhancao and @pramodsatya, how is the development going? I wonder whether there is an ETA of the mini-design about how PrestoQueryRunner gets the original SQL query from the Velox query plan with k-range frames. Please let me know if you need code reviews or if there is something I could help. I'm happy to chat.

Hi @kagamiori, apologies for the delay, we have a prototype PR here and the design document is linked. I have also pushed the changes to this PR. Could you please help review the changes?

[stale[bot]]

This pull request has been automatically marked as stale because it has not had recent activity. If you'd still like this PR merged, please comment on the PR, make sure you've addressed reviewer comments, and rebase on the latest main. Thank you for your contributions!

[pramodsatya]

Closing this in favor of https://github.com/facebookincubator/velox/pull/10006 .