Kim Sandell (Celarius) escreveu:

>
>
> Hi,
>
> I've got a few SQL Queries that behave strangely with FB v2.5 Beta 2:
>
> 1st the OK situations:
>
> 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

First things first, you need a ORDER BY before you can say
that. Otherwise, your result are just incidental, as any
update on those records(even not on Dur_Total) can make
these results change, so they are NOT reliable AT ALL.

That said...

>
> Now the problem statements:
>
> 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...

As I understand, the First..Skip thing works this way :
1 - Execute the query as without FIRST..SKIP
2 - Take the resultset and skip the desired rows
3 - Read the FIRST n rows

Under that premise, that part is correct. Since you skipped
the only record existant, what you expected than empty resultset?

> And other non-working statements:
>
> 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....

Those strange results are kinda bizarre, I agree...
Hmmmm... I though that kind of query would gain an "invalid syntax"
or "FIRST..SKIP cannot operate on a singleton aggregate without an GROUP
BY and/or ORDER BY clause" message...
Those queries deserve it......

>
> 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?