Hi, I've written a simple query and can't explain the results.
The query is:

select sign(iif(rand() > 0.5, 1, -1)), count(*)
from integers(1, 10)
group by 1

sign() and rand() are UDFs from ib_udf

integers is selectable procedure that returns 10 rows, you can put
any table instead and the results are the same (meaning the same
behaviour because of course on each run rand() returns random numbers.

I expected the query would return 2 rows like this:
sign, count
-----------
1, 7
-1, 3
where count values are "random" but the sum is 10.

Instead, i get random number of rows, even more than 10 which is
most surprising of all. The cum of count is always 10. 10 is of
course a number of rows from underlying table, if you run a query
with different number of rows, you'll get that number as result.

If I run the following:
select sign(iif(rand() > 0.5, 1, -1))
from integers(1, 10)
i do get expected results, meaning 10 rows with random 1 and -1
as values.

The server is 2.0.0. I'm pretty sure I'm missing something here
but I don't what so I ask you if anybody can explain it. I haven't
tested it on some other version but I can set up 2.0.1 or 2.1 alpha.
Thanx,

Sasha