3:19 a.m.
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
10:53 a.m.
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:
2:43 a.m.
On 2019-06-24 17:53, Steve Ebersole wrote:
The query is selecting ids, so I assume you mean `1,2,3` as the
expectation.
The test is asserting the result of `ROW_NUMBER() OVER( PARTITION BY
DISCOUNT_CODE ORDER BY SIGN(DISCOUNT_VALUE) DESC )`, and given the
`ORDER BY SIGN(DISCOUNT_VALUE) DESC` and the values of `DISCOUNT_VALUE`
all being positive, this means it orders by (literal) 1. This results in
an implementation specific order (possibly with optimizer or storage
related effects on ordering).
The result expected by the test is:
(ID, DISCOUNT_CODE, DISCOUNT_VALUE, formula)
(1, "20", 12.34, 1)
(2, "20", 15.89, 2)
(3, "100", 12.5, 1)
The result produced by Firebird (and, going by another reply, MariaDB)
is:
(ID, DISCOUNT_CODE, DISCOUNT_VALUE, formula)
(1, "20", 12.34, 2)
(2, "20", 15.89, 1)
(3, "100", 12.5, 1)
Both seem to fulfill the requirement of the test. Changing the test to
`ORDER BY DISCOUNT_VALUE DESC` or `ORDER BY DISCOUNT_VALUE DESC, ID`
would make the order of the result deterministic and possible to assert.
Clearly Firebird cannot support what the query is attempting to do -
so the best option is to skip this test for Firebird.
Firebird can support what the query is attempting to do, but I guess
that the current test is asserting an implementation-specific order.
A deterministic order would make asserting easier, so having an order by
on top-level and in the window function would be better than having
none.
Mark
2101
days inactive
2105
days old
2 comments
2 participants
participants (2)
-
Mark Rotteveel -
Steve Ebersole