Richard, Before you "fix" it, I'm not convinced it is broken.
>From MS SQL server
create table _names (N varchar(5));
insert into _names values('a');
insert into _names values('b');
insert into _names values('c');
insert into _names values('d');
insert into _names values('e');
select N, RAND() as RNDValue from _names
a 0.301745013642105
b 0.301745013642105
c 0.301745013642105
d 0.301745013642105
e 0.301745013642105
select N, RAND() as RNDValue from _names where RAND() >= 0.5;
a 0.0427909435260437
b 0.0427909435260437
c 0.0427909435260437
d 0.0427909435260437
e 0.0427909435260437
>From the SQL manual:
" When you use an algorithm based on GETDATE to generate seed values,
RAND can still generate duplicate values if the calls to RAND are made
within the interval of the smallest datepart used in the algorithm. This
is especially likely when the calls to RAND are included in a single
batch. Multiple calls to RAND in a single batch can be executed within
the same millisecond. This is the smallest increment of DATEPART. In
this case, incorporate a value based on something other than time to
generate the seed values."
So, it comes down to definition:
I would expect that multiple calls to random always return different
values, even if in the same line.
So the following should give different results for each call to random()
Select random(), random(), random()
And the following as well:
Select random() where random() >0
The fact that we are aliasing random by a column name makes no
difference to me.
Therefore: If the "random" function in sqlite is defined as having a
different value every time it is called, and we explain that this is
true, even if aliased, then the current implementation works correctly
and no work, other than documentation is needed.
Regards,
Noah
-----Original Message-----
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]
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
