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
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
