Please clarify with specific examples. I tested some simple use cases and they seemed to work ok. I assume that there are specific syntax or data use cases that might not work?

Build tested: 1.0.4-1 (beta1)

I did another round of tests and the 5 mentioned windowing functions still producing the non-matching results compared to Oracle's results.

The test uses a TPCH 1 mb dataset. For each of the 5 functions, there are lots of queries for the test and many of them failed to match. Below are the first failed query for each of the 5 functions.

select o_custkey, First_value(o_orderkey) OVER ( ORDER BY o_custkey ASC ROWS 15 PRECEDING) from orders;
select o_custkey, Lag(o_orderkey,1) OVER (PARTITION BY abs(o_custkey)+10 ORDER BY o_custkey ) from orders;
select o_custkey, Last_value(o_orderkey) OVER ( ORDER BY o_custkey ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from orders;
select o_custkey, Lead(o_orderkey,1) OVER (PARTITION BY abs(o_custkey)+10 ORDER BY o_custkey ) from orders;
select o_custkey, Nth_value(o_custkey,2) OVER ( ) from orders;

I think we can start with investigating into these 5 queries. If issues found and fixed, I can run the test again to see if all other queries will be fixed.

When testing, one thing to be careful is that the order of the returned values could be different between Oracle and ColumnStore. We have been sorting both sets of results for comparison.

Let me know if additional info is needed.

A problem here is that the results are going to be inconsistent between 2 reference systems and even within different instances of columnstore. This is because order by in a window function is generally supposed to be performed on a meaningfully ordered column that will provide a consistent order. By ordering on the customer key this is not meaningfully ordered since it's not at all unique and so the source data may be in arbitrary order resulting in different but valid results.

For testing i'd suggest we order by orderKey to be completely safe. Partitioning over custKey is a good example.

Moving to in test to verify, no code changes were made.https://mariadb.com/kb/en/mariadb/columnstore-window-functions/ documents behavior.