[hibernate-dev] Test FormulaWithPartitionByTest seems to rely on implementation specific ordering
mark at lawinegevaar.nl
Sat Jun 22 04:19:13 EDT 2019
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:
formulawit0_.id as id1_0_,
formulawit0_.DISCOUNT_CODE as DISCOUNT_CODE2_0_,
formulawit0_.DISCOUNT_VALUE as DISCOUNT_VALUE3_0_,
ROW_NUMBER() OVER( PARTITION
SIGN(formulawit0_.DISCOUNT_VALUE) DESC ) as formula0_
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?
More information about the hibernate-dev