[hibernate-dev] Fwd: Test FormulaWithPartitionByTest seems to rely on implementation specific ordering
Didn't include the list on my original email, sorry.
---------- Forwarded message ---------
From: Graham Collinson <graham_hibernate(a)collo.me.uk>
Date: Tue, 25 Jun 2019 at 02:56
Subject: Re: [hibernate-dev] Test FormulaWithPartitionByTest seems to rely
on implementation specific ordering
To: Steve Ebersole <steve(a)hibernate.org>
Hi,
I get similar results when trying this as a manual test in mariadb
(10.3.8-MariaDB).
The full results returned are:
+--------+-------------------+--------------------+-----------+
| id1_0_ | DISCOUNT_CODE2_0_ | DISCOUNT_VALUE3_0_ | formula0_ |
+--------+-------------------+--------------------+-----------+
| 1 | 20 | 12.34 | 2 |
| 2 | 20 | 15.89 | 1 |
| 3 | 100 | 12.5 | 1 |
+--------+-------------------+--------------------+-----------+
If the test is against the row_number (or formula0_) column then expecting
1,2,1 appears to be incorrect.
The row_number is over a partition on discount code with rows ordered by
sign(discount_value) descending.
As Mark says sign(discount_value) is always 1. So should this query lead
to the list of row_numbers expected by the test?
I tested in oracle (11.2.0.3) and got the order the test expects.
ID1_0_ DISCOUNT_CODE2_0_ DISCOUNT_VALUE3_0_ FORMULA0_
1 20 12.34 1
2 20 15.89 2
3 100 12.5 1
It looks like it may be a bit random to me.
Regards,
Graham
On Mon, 24 Jun 2019 at 16:56, Steve Ebersole <steve(a)hibernate.org> wrote:
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(a)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(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev