The query is selecting ids, so I assume you mean `1,2,3` as the expectation.
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 <m...@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: > 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 > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
