| Subject | Re: [firebird-support] Strange FIRST n SKIP m + Function behaviour in FB 2.5 Beta 2 |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-09-22T23:12:36Z |
At 12:23 AM 23/09/2009, you wrote:
Since you didn't include an ORDER BY clause, you would just get the result back in no particular order.
As Alan said, don't try to apply FIRST...SKIP to the results of aggregations that return a singleton result, since its purpose is to operate on ordered sets of multiple rows.
If you're interested in continuing to test {whatever you had in mind}, think through what you're actually looking for.
For FIRST, answer the question "first of what?" Since stored data in relational databases has no implicit "order", you have to specify the order that you want FIRST to apply to. ORDER BY produces an ordered set; so does GROUP BY, although there is no expectation of what ordering you get from GROUP BY in the absence of an ORDER BY. (In practice, GROUP BY usually orders the output in ascending order according to the data type, but it should not be relied on for order-critical operations such as FIRST...SKIP.)
For aggregations, figure out the grouping that you want to aggregate on. Since your examples return only one column, the only option you have to return a non-singleton set is to aggregate on (GROUP BY) that column. Then (notwithstanding the lossiness of aggregating on FLOAT!!) FIRST...SKIP would have a non-singleton set to work on. However, even if you were aggregating on fixed numerics, this group of examples represents a futile exercise: you would just as well get the intended result without the aggregation.
./heLen
>Hi,Strange (read "illogical") queries do tend to return strange results! ;-)
>
> I've got a few SQL Queries that behave strangely with FB v2.5 Beta 2:
> 1st the OK situations:These are sets that FIRST n ...SKIP m can work with. This operation takes an ordered set, bypasses the first m rows in that set and returns the following n rows.
>
> SELECT FIRST 3 Dur_Total FROM HTTP_Events
> gives 3 result rows: 0.5575, 1.2298, 0.7567
>
> SELECT FIRST 2 SKIP 1 Dur_Total FROM HTTP_Events
> gives 2 result rows: 1.2298, 0.7567
Since you didn't include an ORDER BY clause, you would just get the result back in no particular order.
> Now the problem statements:These are all aggregation operations that return a singleton result, so your expectation of the "correct result" isn't logical.
>
> SELECT FIRST 2 SKIP 1 Min(Dur_Total) FROM HTTP_Events
> gives 0 result rows????? Correct answer would be 1 row with value 0.7567
> The same happens with MIN(), MAX(), AVG(), SUM() etc...
> Event COUNT() gives an empty result...
> And other non-working statements:My impression is, it's a mix of not understanding aggregation and not understanding what FIRST..SKIP is designed to achieve.
>
> SELECT FIRST 1 SKIP 0 Min(Dur_Total) FROM HTTP_Events
> gives 1 result row: 0.0000 - Which is wrong, should be 0.5575
>
> SELECT FIRST 1 SKIP 0 Avg(Dur_Total) FROM HTTP_Events
> gives 1 result row: 9.7764 - Wrong again ...
>
> SELECT FIRST 1 SKIP 0 Max(Dur_Total) FROM HTTP_Events
> gives 1 result row: 62.1193 - This is even stranger....
>
> FB version: WI-T6.3.0.24643 Firebird 2.5 Beta 2
>
> The HTTP_Events table DUR_Total field is a FLOAT.
>
> Has anyone encountered this before? Or am I using the MIN,MAX,AVG etc.
>functions wrong somehow?
As Alan said, don't try to apply FIRST...SKIP to the results of aggregations that return a singleton result, since its purpose is to operate on ordered sets of multiple rows.
If you're interested in continuing to test {whatever you had in mind}, think through what you're actually looking for.
For FIRST, answer the question "first of what?" Since stored data in relational databases has no implicit "order", you have to specify the order that you want FIRST to apply to. ORDER BY produces an ordered set; so does GROUP BY, although there is no expectation of what ordering you get from GROUP BY in the absence of an ORDER BY. (In practice, GROUP BY usually orders the output in ascending order according to the data type, but it should not be relied on for order-critical operations such as FIRST...SKIP.)
For aggregations, figure out the grouping that you want to aggregate on. Since your examples return only one column, the only option you have to return a non-singleton set is to aggregate on (GROUP BY) that column. Then (notwithstanding the lossiness of aggregating on FLOAT!!) FIRST...SKIP would have a non-singleton set to work on. However, even if you were aggregating on fixed numerics, this group of examples represents a futile exercise: you would just as well get the intended result without the aggregation.
./heLen