Subject | Strange result of aggregating rand() values |
---|---|

Author | selectnull |

Post date | 2007-05-18T11:26:34Z |

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

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