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

[Steve Ebersole]

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 <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:
>      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 at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>