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

[Graham Collinson]

Didn't include the list on my original email, sorry.

---------- Forwarded message ---------
From: Graham Collinson <graham_hibernate at 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 at 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 at 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 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
> >
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>