Date: 2015-10-26 15:23:01 +0100
From: Robin Cijvat <<robin.cijvat>>
To: MonetDB5 devs <>
Version: 11.21.5 (Jul2015)
CC: @mlkersten

Last updated: 2016-01-15 11:38:22 +0100

Comment 21407

Date: 2015-10-26 15:23:01 +0100
From: Robin Cijvat <<robin.cijvat>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.27 Safari/537.36
Build Identifier:

This SQL generates a column with 2 equal results, because apparently the rand() operator is considered a constant:

create table t(a int);
insert into t values (1), (1);
select a + rand() from t;

Further examples:

rand() seems to be treated a "constant" only when in an expression;
see below ...

sql>create table t (a int, b int default rand());
operation successful (8.297ms)
sql>insert into t(a) values (1);
1 affected row (5.830ms)
sql>insert into t(a) values (2);
1 affected row (5.175ms)
sql>insert into t(a) values (3);
1 affected row (5.258ms)
sql>alter table t add column c int default rand();
operation successful (11.170ms)
sql>alter table t add column d int default null;
operation successful (10.695ms)
sql>select * from t;
+------+------------+------------+------+
| a | b | c | d |
+======+============+============+======+
| 1 | 2018667017 | 1223444628 | null |
| 2 | 1184696955 | 1855771568 | null |
| 3 | 758492986 | 1856268030 | null |
+------+------------+------------+------+
3 tuples (1.891ms)

sql>update t set d = rand(); -- works as expected
3 affected rows (7.684ms)
sql>select * from t;
+------+------------+------------+------------+
| a | b | c | d |
+======+============+============+============+
| 1 | 2018667017 | 1223444628 | 2132096871 |
| 2 | 1184696955 | 1855771568 | 465839912 |
| 3 | 758492986 | 1856268030 | 1856154042 |
+------+------------+------------+------------+
3 tuples (2.123ms)

sql>update t set a = a + rand(); -- does not work as expected/supposed to
3 affected rows (7.515ms)
sql>select * from t;
+----------+------------+------------+------------+
| a | b | c | d |
+==========+============+============+============+
| 12809903 | 2018667017 | 1223444628 | 2132096871 |
| 12809904 | 1184696955 | 1855771568 | 465839912 |
| 12809905 | 758492986 | 1856268030 | 1856154042 |
+----------+------------+------------+------------+
3 tuples (3.547ms)

Reproducible: Always

Steps to Reproduce:

See details

Comment 21478

Date: 2015-11-08 11:10:57 +0100
From: @mlkersten

Bug added to BugTracker-2015/Tests/constant-random.Bug-3836.sql

Comment 21702

Date: 2015-12-30 13:31:35 +0100
From: @mlkersten

Seems closed.