Date: 2018-09-20 11:37:56 +0200
From: daniel.zvinca
To: SQL devs <>
Version: 11.31.7 (Aug2018)
CC: daniel.zvinca, @njnes

Last updated: 2019-01-14 17:29:10 +0100

Comment 26618

Date: 2018-09-20 11:37:56 +0200
From: daniel.zvinca

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36
Build Identifier:

functions like uuid() or rand() suppose to return different results for every invocation. it seems the optimizer evaluates the function just once and use the result for subsequent calls. They might be just a few standard functions with this undesirable behavior, but issue becomes more serious for udf added functions (capi, Python, R)

Reproducible: Always

Steps to Reproduce:

1.CREATE TABLE test (x integer);
2.INSERT INTO test VALUES (0), (1);
3.SELECT uuid(), uuid() from test;

Actual Results:

4883689d-5143-3244-aa05-9403a1e7607b, 4883689d-5143-3244-aa05-9403a1e7607b
4883689d-5143-3244-aa05-9403a1e7607b, 4883689d-5143-3244-aa05-9403a1e7607b

Expected Results:

I agree that most of the functions were meant to be reduced to constant. A flag like "not_constant" can exclude a handful of functions from being replaced by their first constant evaluation. I expected varres=true settled in functions table for name=uuid to provide the desired result. It did not.

They might be only a few functions as standard that require review, but with udf extensions (C, Python, R), the list can grow very fast. Imagine functions that are passing as parameter a pointer to certain struct that changes with every iteration. Pointer stays the same, but contents can change and the function results also.

The only workaround I could think of was:

CREATE FUNCTION MY_UUID(fake int)
RETURNS UUID
BEGIN
return uuid();
END;

select my_uuid(x), my_uuid(x+1) from test; -- different params

or

CREATE SEQUENCE "my_fake_seq" as integer CYCLE;

select my_uuid(NEXT VALUE FOR "my_fake_seq"), my_uuid(NEXT VALUE FOR "my_fake_seq") from test;

results (different as expected)
| e3e6e4d9-47e3-b78f-c6f8-9845812025e4 | 50bcaa05-fe50-f6bb-20fa-8f9e9e49357f |
| cbf7c4a2-f1db-200b-2530-906731962d97 | 17bed98c-228f-05e7-2542-abc2b7ad7882 |

rand() behaves even more unexpected. Try below.
SELECT rand(), rand() from test;
SELECT rand(1000), rand(1000) from test;

Comment 26735

Date: 2018-12-19 14:21:44 +0100
From: MonetDB Mercurial Repository <>

Changeset 23e1231ada99 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=23e1231ada99

Changeset description:

fixes for bug #6645, ie make sure we call uuid per row (do not reduce
the number of calls using any of the common expression optimizers)

Comment 26736

Date: 2018-12-19 14:23:31 +0100
From: @njnes

Fixed, ie uuid and any other argumentless function will be treaded as non constant.

The weirdness of the rand isn't the same. The rand() works and the
rand(seed) also, but pseudo random number generators which get initialized
again and again will return the same result.

Comment 26737

Date: 2018-12-20 05:20:07 +0100
From: daniel.zvinca

I still consider that a mechanism that would identify constant functions would be the right design. A function defined as constant will be calculated once if it has no parameters or of its parameters are all constant. That would cover all cases.

As for the rand(), maybe initializing pseudo random number generators only once when db is connected would solve the issue? Not sure if this makes sense, I can check a bit some sources to see if that would be a viable solution. You need to agree that is quite unusual to find utility in a function that suppose to return random numbers, but in fact it does not. Just a thought.