mySQL differs from MS SQL in it random function handling:
SELECT i, RAND() AS R1, RAND() AS R2 FROM z where RAND() < .4;
i R1 R2
1 0.531666 0.692986
3 0.743755 0.906643
4 0.789811 0.04321
6 0.977431 0.576784
8 0.284047 0.336876
Different values for R1 and R2 (each call to RAND() produces different
values), as Noah expects, including the RAND() criterion.
However, the following output differs from the MS SQL results:
SELECT T1.i, T1.RNDValue
FROM (select i, RAND() AS RNDValue from z) T1
WHERE T1.RNDValue > .7
i RNDValue
5 0.902673
9 0.799401
This apparently only calls RAND() once. It consistently returns only
distinct values for RNDValue, all being greater than .7.
Maybe it is a matter of documentation rather than conforming to some
standard practice.
Chris
On Fri, 29 Aug 2008, Noah Hart wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
