Hi, Indeed, rand is an 'unsafe' function and should not be optimized away by the CSE. On 21/10/15 08:25, Stefan Manegold wrote:
Hi Robin,
CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)
is an expression that does not depend on the table data; hence, it's assumed to be constant and evaluated only once.
The solution would be to make this expression (look as if it is) dependent on the data (i.e., ts). E.g., one could change the SQL front-end or MAL optimizers (CSE?) to not treat rand() as a "constant" (data-independent function call), or one could make rand() accept (and ignore) an optional argument (of any type?); then rand(ts) should be evaluated with each row.
Alternative: ALTER TABLE "table" ADD COLUMN "r" INT DEFAULT RAND(); UPDATE "table" SET ts = (ts + CAST( CAST(("r"/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)); (ALTER TABLE "table" DROP COLUMN "r";)
Stefan
----- On Oct 21, 2015, at 7:13 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Hi Stefan and Sjoerd, thanks for the help! By the way, I found out that rand() returns an integer, which also seems to be the same across platforms, so I wrote the following SQL, which almost works:
UPDATE "table" SET ts = (ts + CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND));
The problem here is that the expression only gets evaluated once, hence I end up with the same 'random' addition in all tuples. Is there any way I could trick MonetDB into not reusing the evaluated expression?
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:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
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
- -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJmPoAAoJEISMxT6LrWYgCzkH/jm6+xRYh93Om78w5UZTEV84 E9aBApWr3arH9QNAmqrQf0PvHcdh3g/nDTiEktQeMLcm8u4+d62G6KS4m19xWy71 KiI6LTtFuMpv3Ax9WN7U7ji95w4/XEcuooW7RBqHWHbPo8FooAwJSxHxWoSKXQ/6 49nHZRO5lD3Ydfnse/mMH32irIMau3lBgsX/cCptZkxpsthNspfpuQ7CsrmTF0EZ BK/NkQRymNRmLnDeA9yYs+HCGSEdNrB/cYoAb9CDkcwYbrgbbvWcTJewahZsX+TR QlKuDvvldhVtIpJCAsHQ/vksGpqmoVAM3hl/S5JQ1FDzZPyKQAQjaw/Ze7s6iy0= =vG5t -----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