In SQL standard? I don't know. In MonetDB's SQL implementation: not right now (AFAIK). We/you could consider exposing libc macro RAND_MAX as standard SQL constant (variable?) --- provided it is indeed available on all platforms ... ? Or you just assume (or better double-check) that rand() on most/all "standard" systems does (at least) produce random values between 0 & 2^31-1 ... Stefan ----- On Oct 21, 2015, at 10:40 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Yes, but is there a way I could fetch this maximum value within SQL? Without having to know what system I am running on?
On 21-10-15 10:31, Stefan Manegold wrote:
MonetDB's SQL RAND() function returns type int:
sql>select * from functions where name = 'rand'; +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ | id | name | func | mod | language | type | side_effect | varres | vararg | | schema_id | +======+======+=========+=======+==========+======+=============+========+========+===========+ | 867 | rand | rand | mmath | 0 | 1 | true | false | false | | 0 | | 868 | rand | sqlrand | mmath | 0 | 1 | true | false | false | | 0 | +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ 2 tuples (3.151ms) sql>\fraw sql>select rand(); % .L # table_name % rand # name % int # type % 9 # length [ 563202665 ]
i.e., the max possible value is the minimum of 2^31-1 and the max value that the underlying systems's (libc's) rand() function returns (for Linux and Windows see what Sjoerd wrote).
----- On Oct 21, 2015, at 10:19 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Thanks for all your help and suggestions! I will file a bug report and use the workaround proposed by Stefan for now.
@Sjoerd: thanks for the info, that means I cannot just hard code a value to divide the RAND output by to normalize.
Does anyone know of a way to get the maximum returned value by a call to RAND() in SQL?
On 21-10-15 09:13, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
The range of RAND is system dependent. On Linux (glibc) it's 0 - 2^63-1, but on Windows it's only 0 - 2^31-1. I don't know for other platforms.
On 20/10/15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
On 20/10/15 16:51, Robin Cijvat wrote:
>> Hello everyone! I am facing some (probably simple) problems. >> A snippet of a timestamp column in my table: >> >> +----------------------------+ | ts >> | +============================+ | 1595-04-02 00:00:00.000000 >> | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 >> | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 >> | +----------------------------+ >> >> I want to fill all the times in these fields with a random >> value (uniformly distributed is fine). This faces me with the >> following problems: >> >> 1) Adding an interval to a timestamp does not seem to be >> suited for dynamic intervals: This works fine: UPDATE "table" >> SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE >> "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) >> SECOND); Does someone maybe know of a way to accomplish >> this? >> >> 2) Does someone know how to normalize the output of the RAND >> operator available in SQL? It seems to calculate a random >> BIGINT, and in order for me to normalize it, I would need the >> maximum value of a BIGINT, which of course is platform >> dependent. Is there a way to get the max value of BIGINT on >> the SQL level? Or can I go about this another way? >> >> >> All help is much appreciated! >> >> Robin sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
>> >> >> _______________________________________________ users-list >> mailing list users-list@monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJzr8AAoJEISMxT6LrWYghCwIAL7QiRsOmorYkxXDSnT686iJ zn9Wco//qf0xWaGKHMimhXP0LWX9SN1rJSOckZXaFYC+mTdumhzBgRwSop46cNYZ u+xoRzoqYoKpTcEjZekJ97pjMADUU3np6sUbx0lwbr1ELHYJ08KmLJk7gxF9YRxp UGjsMt7kJQ5rnvfFlodOPuNZEj1uG5KDR92eQTP59wt5d9NweiKaPvntSuJWp78W aiOlkdgHtqEjn1qMvwTwQvxGkKQrW/q+Q9W9prc9WVIUc6WOaM/KOuS1JKkWe1Kp wqVbbsTOYekyH4xxOmLjpyJnO7p+qNZYyX4xs7+fHSRTnUjFBN+A3qQWIaSAYrA= =Lspw -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |