Oracle Database Free

Hi,

We've identified an issue with the LAG(value_expr, offset, default) window function in Oracle Database 23ai Free (version 23.5.0.24.07).

According to the official documentation, LAG() should return a row at a given physical offset prior to the current row, or NULL if the offset exceeds the window, unless a default is specified.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LAG.html

However, we've obeserved that it returns a row after the current row when the offset is greater than or equal to 18446744073709551615 (0xFFFFFFFFFFFFFFFF), which deviates from both the documentation and expected behavior. Additionally, we found that this error takes priority even over the explicit default value.

Steps to Reproduce:

The issue can be reproduced with the following minimized query:

CREATE TABLE v0 (v1 NUMBER);
INSERT INTO v0 (v1) VALUES (0), (1), (2), (3), (4);
SELECT v1, LAG(v1, 18446744073709551615) OVER (ORDER BY v1) FROM v0;        -- wrong (0xFFFFFFFFFFFFFFFF)
SELECT v1, LAG(v1, 18446744073709551615, -1) OVER (ORDER BY v1) FROM v0;    -- wrong, even with default specified (0xFFFFFFFFFFFFFFFF)

Expected Result:

SQL> SELECT v1, LAG(v1, 18446744073709551614) OVER (ORDER BY v1) FROM v0;

        V1 LAG(V1,18446744073709551614)OVER(ORDERBYV1)
---------- -------------------------------------------
         0
         1
         2
         3
         4

SQL> SELECT v1, LAG(v1, 18446744073709551614, -1) OVER (ORDER BY v1) FROM v0;

        V1 LAG(V1,18446744073709551614,-1)OVER(ORDERBYV1)
---------- ----------------------------------------------
         0                                             -1
         1                                             -1
         2                                             -1
         3                                             -1
         4                                             -1

When using an out-of-window offset, LAG() should return NULL for all rows or the specifed default value. (Note that the offset 18446744073709551614, one less than 18446744073709551615, behaves normally.)

If the offset is unsupported, LAG() should return an error with an appropriate message.

Actual result (Oracle Database 23ai Free, version 23.5.0.24.07):

SQL> SELECT v1, LAG(v1, 18446744073709551615) OVER (ORDER BY v1) FROM v0;

        V1 LAG(V1,18446744073709551615)OVER(ORDERBYV1)
---------- -------------------------------------------
         0                                           1
         1                                           2
         2                                           3
         3                                           4
         4


SQL> SELECT v1, LAG(v1, 18446744073709551615, -1) OVER (ORDER BY v1) FROM v0;

        V1 LAG(V1,18446744073709551615,-1)OVER(ORDERBYV1)
---------- ----------------------------------------------
         0                                              1
         1                                              2
         2                                              3
         3                                              4
         4                                             -1

However, Oracle Database returns a row after the cursor, even ignoring the default value, which LAG() is NOT intended to return.

We believe this behavior is a bug with significant security implications, as offset is restricted to positive integers and LAG() should never return upcoming rows in its window scope for any valid offset.

We suggest updating Oracle Database to handle all valid offsets correctly, either by returning NULL (or default, if specified) for out-of-window values or raising an error with an appropriate message.

Thank you for your time and attention to this matter.

We look forward to your response.

Best regards.