CODEWITHSUNDEEP
sqlpostgresql-9.3
Adam Mackler
Adam Mackler

Reputation: 2080

Why does random() function in where-clause cause query to return more or less than one row?

How is it possible that this query can return more than one row? How can it return zero rows?

WITH cte(k,v) AS (VALUES (0,'A'), (1,'B'), (2,'C'))
SELECT * FROM cte WHERE k=round(random()*2);

Sometimes it returns one row, sometimes two, or three, sometimes none. But if the random() function is returning one value, that value can match no more than one row in the table, right? And if I multiply it by 2 and round to an integer, then the returned value must be either 0, 1 or 2 and thus must match exactly one row, right?

If I put the random() invocation inside a subquery like this:

WITH cte(k,v) AS (VALUES (0,'A'), (1,'B'), (2,'C'))
SELECT * FROM cte WHERE k=(select round(random()*2));

then it only ever returns exactly one row, just as I would expect without the subquery.

And the query

SELECT round(random()*2);

only ever returns one row, with one column whose value is either 0, 1, or 2, so I am not understanding how that can match anything but one row in the cte table. Yet in the first query above, sometimes it seems to be matching more or less than one row. What is going on here?

Upvotes: 2

Views: 164

Answers (2)

Adam Bengtsson
Adam Bengtsson

Reputation: 156

As stated by others, the random() is called for every row.

If you want a single random row you could use an ORDER BY random() clause and then LIMIT the result to a single row, something like this (not tested):

WITH cte(k,v) AS (VALUES (0,'A'), (1,'B'), (2,'C')) SELECT * FROM cte ORDER BY random() LIMIT 1;

Upvotes: 3

Frank Schmitt
Frank Schmitt

Reputation: 30775

Presumably, random() is called for every row in your source data and not just once (like you assumed).

So, every row in your source data has a 33% chance of being returned.

Upvotes: 2

Related Questions