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

[Mark Rotteveel]

The test FormulaWithPartitionByTest against Firebird 3 fails because the 
returned result has a different order than the one expected by the test.

As far as I can tell, the order expected by the test is arbitrary or at 
least, as far as I can tell, the order expected is not necessarily 
required by the SQL standard (although it might as well be a bug in 
Firebird, I'm just not sure).

The testdata is
(ID, DISCOUNT_CODE, DISCOUNT_VALUE)
(1, "20", 12.34)
(2, "20", 15.89)
(3, "100", 12.5)

With generated query:
     select
         formulawit0_.id as id1_0_,
         formulawit0_.DISCOUNT_CODE as DISCOUNT_CODE2_0_,
         formulawit0_.DISCOUNT_VALUE as DISCOUNT_VALUE3_0_,
         ROW_NUMBER() OVER( PARTITION
     BY
         formulawit0_.DISCOUNT_CODE
     ORDER BY
         SIGN(formulawit0_.DISCOUNT_VALUE) DESC ) as formula0_
     from
         DisplayItem formulawit0_
     order by
         formulawit0_.id

The expected order of the row_number() is 1, 2, 1 but due to the 
evaluation order in Firebird of order by in window functions and the top 
level order by, the resulting order is 2, 1, 1.

I can see four solutions for this:

1. Just ignore the test for Firebird 3
2. Add a Firebird specific expectation of order (although that would be 
testing what is likely an implementation artifact)
3. Enforce a more specific order in the window function by changing the 
order by to ORDER BY SIGN(DISCOUNT_VALUE) DESC, ID
4. Enforce an order by that is consistent with the data: ORDER BY 
DISCOUNT_VALUE (the use of SIGN with the shown data can lead to an 
arbitrary order as the result is always 1).

What would have your preference?

Mark
-- 
Mark Rotteveel