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

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