On Aug 28, 2008, at 12:00 PM, Igor Tandetnik wrote:
> 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.
>
OK. Even though this kind of thing is probably an abuse of SQL, I'm
working on ticket #3343. Just for the record, I'd like everybody to
know that the following is really, really hard to do correctly and is
going to require a lot of extra code in SQLite - code that nobody will
ever use in practice:
SELECT random()%5 AS x, count(*) FROM tab GROUP BY x;
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
