Tmb 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.
>
> If I execute the above mentioned query, I get a result like this:
>
> Name RNDValue
> Name1 0.23...
> Name2 0.521...
> Name3 0.429...
> Name4 0.725...
> Name5 0.149...
>
> Is this an error within SQLite or am I just misunderstanding it?
>
> Thank you for your suggestions/comments.
The problem you are seeing is due to the fact that SQLite substitutes
the expression for RNDValue into the where clause, not the value. You
can see this if you do an explain.
SQLite version 3.6.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table names(name text);
sqlite> .explain on
sqlite> explain select name, (random() / 9223372036854775807.0 + 1.0) /
2.0 as R
NDValue from
...> names where RNDValue >= 0.99;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 explain select name, (random() /
92233720
36854775807.0 + 1.0) / 2.0 as RNDValue from
names where RNDValue >= 0.99; 00
1 Real 0 1 0 9.223372036854778e+18 00
2 Real 0 2 0 1 00
3 Real 0 3 0 2 00
4 Real 0 4 0 0.99 00
5 Goto 0 26 0 00
6 SetNumColumns 0 1 0 00
7 OpenRead 0 2 0 00
8 Rewind 0 24 0 00
9 Function 0 0 8 random(-1) 00
10 Divide 1 8 7 00
11 Add 2 7 6 00
12 Divide 3 6 5 00
13 Lt 4 23 5 6a
14 Column 0 0 10 00
15 Function 0 0 7 random(-1) 00
16 Real 0 8 0 9.223372036854778e+18 00
17 Divide 8 7 6 00
18 Real 0 8 0 1 00
19 Add 8 6 5 00
20 Real 0 8 0 2 00
21 Divide 8 5 11 00
22 ResultRow 10 2 0 00
23 Next 0 9 0 00
24 Close 0 0 0 00
25 Halt 0 0 0 00
26 Transaction 0 0 0 00
27 VerifyCookie 0 1 0 00
28 TableLock 0 2 0 names 00
29 Goto 0 6 0 00
sqlite>
There are two calls to the random function, one is used in the where
clause, the other is used to calculate the value to put in the result row.
Igor suggested using a subselect,but that has the same issue.
sqlite> explain select name, RNDValue
...> from (
...> select name, (random() / 9223372036854775807.0 + 1.0) / 2.0 as
...> RNDValue from names
...> )
...> where RNDValue >= 0.99;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 explain select name, RNDValue
from (
select name, (random() / 9223372036854775807.0 + 1.0) / 2.0 as
RNDValue from names
)
where RNDValue >= 0.99; 00
1 Real 0 1 0 9.223372036854778e+18 00
2 Real 0 2 0 1 00
3 Real 0 3 0 2 00
4 Real 0 4 0 0.99 00
5 Goto 0 26 0 00
6 SetNumColumns 0 1 0 00
7 OpenRead 1 2 0 00
8 Rewind 1 24 0 00
9 Function 0 0 8 random(-1) 00
10 Divide 1 8 7 00
11 Add 2 7 6 00
12 Divide 3 6 5 00
13 Lt 4 23 5 collseq(BINARY) 6a
14 Column 1 0 10 00
15 Function 0 0 7 random(-1) 00
16 Real 0 8 0 9.223372036854778e+18 00
17 Divide 8 7 6 00
18 Real 0 8 0 1 00
19 Add 8 6 5 00
20 Real 0 8 0 2 00
21 Divide 8 5 11 00
22 ResultRow 10 2 0 00
23 Next 1 9 0 00
24 Close 1 0 0 00
25 Halt 0 0 0 00
26 Transaction 0 0 0 00
27 VerifyCookie 0 1 0 00
28 TableLock 0 2 0 names 00
29 Goto 0 6 0 00
sqlite>
You could try this instead.
-- create temp table with random numbers
create temp table rnd(id integer primary key, RNDValue real);
insert into rnd
select rowid, (random() / 9223372036854775807.0 + 1.0) / 2.0 as
RNDValue from names;
-- select name based on associated random numbers
select name, RNDValue
from names
join rnd on names.rowid = rnd.id
where rnd.RNDValue < 0.99;
-- delete temp table
drop table rnd;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
