Didn't include the list on my original email, sorry. ---------- Forwarded message --------- From: Graham Collinson <graham_hibern...@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 <st...@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 <st...@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 <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 > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev