[hibernate-dev] Test FormulaWithPartitionByTest seems to rely on implementation specific ordering
steve at hibernate.org
Mon Jun 24 11:53:22 EDT 2019
The query is selecting ids, so I assume you mean `1,2,3` as the
Clearly Firebird cannot support what the query is attempting to do - so the
best option is to skip this test for Firebird. 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.
On Sat, Jun 22, 2019 at 3:19 AM Mark Rotteveel <mark at lawinegevaar.nl> wrote:
> 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
> ORDER BY
> SIGN(formulawit0_.DISCOUNT_VALUE) DESC ) as formula0_
> DisplayItem formulawit0_
> order by
> 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 Rotteveel
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
More information about the hibernate-dev