Inconsistent Tie Resolution Across Nested Window Functions

MariaDB 12.3.2 exhibits inconsistent tie-resolution behavior when evaluating multiple window functions within the same query that share identical ORDER BY clauses.

Specifically, when ties exist on the ordering column(s), MariaDB may apply different tie-resolution orders across nested window function (WF) evaluations—one in an inner subquery and another in an outer query. This leads to incorrect results, including violations of expected monotonic properties (e.g., cumulative sums decreasing).

Minimal Reproducer:

CREATE DATABASE mt; USE mt;

CREATE TABLE t (id INT PRIMARY KEY, p INT, o INT, v INT);

INSERT INTO t VALUES

  (1, 1,  5,  100),

  (2, 1, 10,    1),

  (3, 1, 10, 1000),

  (4, 1, 20,    1);

SELECT id, o, cs, prev_cs,

       CASE WHEN prev_cs IS NOT NULL AND cs < prev_cs

            THEN 'VIOLATION' ELSE 'OK' END AS status

FROM (

  SELECT id, o, cs,

    LAG(cs, 1) OVER (PARTITION BY p ORDER BY o) AS prev_cs

  FROM (

    SELECT id, p, o,

      SUM(ABS(v)) OVER (

        PARTITION BY p ORDER BY o

        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

      ) AS cs

    FROM t

  ) s1

) s2

ORDER BY o, id;

Observed Behavior (MariaDB 12.3.2)
id o cs prev_cs status
1 5 100 NULL OK
2 10 101 1101 VIOLATION
3 10 1101 100 OK
4 20 1102 101 OK
Explanation
Inner SUM orders tied rows (o=10) as: id=2 → id=3 → cs = (101, 1101)
Outer LAG orders the same rows as: id=3 → id=2

Thus:

At id=2, LAG incorrectly returns cs=1101 (future row)
This violates monotonicity: cs < prev_cs

Expected Behavior
id o cs prev_cs status
1 5 100 NULL OK
2 10 101 100 OK
3 10 1101 101 OK
4 20 1102 1101 OK

Both window functions use a consistent tie-order (e.g., insertion order), preserving monotonicity.

Invariant Violation

The following invariant should hold:

The cumulative sum of ABS(v) within a partition must be monotonically non-decreasing.

SELECT cs, prev_cs
FROM (<expr>)
WHERE prev_cs IS NOT NULL AND cs < prev_cs;