[hibernate-dev] Test FormulaWithPartitionByTest seems to rely on implementation specific ordering

[Mark Rotteveel]

On 2019-06-24 17:53, Steve Ebersole wrote:
> The query is selecting ids, so I assume you mean `1,2,3` as the
> expectation.

The test is asserting the result of `ROW_NUMBER() OVER( PARTITION BY 
DISCOUNT_CODE ORDER BY SIGN(DISCOUNT_VALUE) DESC )`, and given the 
`ORDER BY SIGN(DISCOUNT_VALUE) DESC` and the values of `DISCOUNT_VALUE` 
all being positive, this means it orders by (literal) 1. This results in 
an implementation specific order (possibly with optimizer or storage 
related effects on ordering).

The result expected by the test is:

(ID, DISCOUNT_CODE, DISCOUNT_VALUE, formula)
(1, "20", 12.34, 1)
(2, "20", 15.89, 2)
(3, "100", 12.5, 1)

The result produced by Firebird (and, going by another reply, MariaDB) 
is:

(ID, DISCOUNT_CODE, DISCOUNT_VALUE, formula)
(1, "20", 12.34, 2)
(2, "20", 15.89, 1)
(3, "100", 12.5, 1)

Both seem to fulfill the requirement of the test. Changing the test to 
`ORDER BY DISCOUNT_VALUE DESC` or `ORDER BY DISCOUNT_VALUE DESC, ID` 
would make the order of the result deterministic and possible to assert.

> Clearly Firebird cannot support what the query is attempting to do -
> so the best option is to skip this test for Firebird.

Firebird can support what the query is attempting to do, but I guess 
that the current test is asserting an implementation-specific order.

> However, the
> order-by is really not serving any purpose to the test aside from
> making the assertions easier; so another option would be to remove the
> order-by and assert the results via iterating them.

A deterministic order would make asserting easier, so having an order by 
on top-level and in the window function would be better than having 
none.

Mark