Feature: Add { IGNORE | RESPECT } NULLS support for some ranking window functions

[TCeason]

Summary

Currently, Databend doesn't support the { IGNORE | RESPECT } NULLS syntax for ranking window functions. This limits the flexibility in handling NULL values within window frames.

This feature request proposes adding support for { IGNORE | RESPECT } NULLS to the following ranking window functions:

• FIRST_VALUE()
• LAG()
• LAST_VALUE()
• LEAD()
• NTH_VALUE()

Similar to Snowflake and PostgreSQL, this syntax will allow users to explicitly control how NULL values are handled within the window frame, providing more consistent and predictable behavior.

Expected Behavior:

• When { IGNORE | RESPECT } NULLS is not specified, the default behavior should be consistent with the SQL standard.
• For functions like LAG() and LEAD(), IGNORE NULLS will skip NULL values and fetch the next/previous non-NULL value within the window frame.
• For functions like FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE(), IGNORE NULLS will attempt to find the first/last/nth non-NULL value within the window frame. If all values are NULL, they should return NULL even if IGNORE NULLS is specified.

Benefits:

• Enhanced SQL compatibility with other database systems.
• More flexibility and control over handling NULL values in ranking window functions.
• Improved consistency and predictability in query results.