Tmb <[EMAIL PROTECTED]> wrote:
> I created a SQLite database where a table 'names' is located. This
> table stores just a single column called 'name'. Now I tried for test
> purposes to create a random number within range [0..1] for each
> record in the table using a SELECT statement like this:
>
> select name, (random() / 9223372036854775807.0 + 1.0) / 2.0 as
> RNDValue from names;
>
> This works fine and the random values are successfully generated
> within the required range. Now, I tried to select just a subset of
> records with a random value >= 0.99 for example (using a WHERE
> condition).
>
> The query looks now like:
>
> select name, (random() / 9223372036854775807.0 + 1.0) / 2.0 as
> RNDValue from names where RNDValue >= 0.99;
>
> The query returns just a few records, which is fine - but the problem
> is, that RNDValues less than 0.99 are returned.
It looks like random() is run twice for each row - once in WHERE clause
and again in the SELECT clause. This looks like a bug.
Try this:
select name, RNDValue
from (
select name, (random() / 9223372036854775807.0 + 1.0) / 2.0 as
RNDValue from names
)
where RNDValue >= 0.99;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
