Weird behavior with RANDOMINT
Posted: Tue Sep 24, 2013 2:17 pm
Hi,
I am experiencing some really weird behavior with the RANDOMINT function.
I have a table named test_date which has two columns. A date_key which is just a number representing a date (YYYYMMDD) and a column called rn (row number).
I want to select a random date_key from the table. I tried this:
It works most of the time, but sometimes I get more than one row:
And sometimes I get no rows:
How is that possible?
There are no duplicates:
Can someone explain what is happening?
Thanks!
I am experiencing some really weird behavior with the RANDOMINT function.
I have a table named test_date which has two columns. A date_key which is just a number representing a date (YYYYMMDD) and a column called rn (row number).
Code: Select all
dbadmin=> select * from test_date order by 1, 2;
date_key | rn
----------+------
19200101 | 1
19200102 | 2
19200103 | 3
19200104 | 4
19200105 | 5
19200106 | 6
19200107 | 7
19200108 | 8
19200109 | 9
19200110 | 10
...
19991221 | 29210
19991222 | 29211
19991223 | 29212
19991224 | 29213
19991225 | 29214
19991226 | 29215
19991227 | 29216
19991228 | 29217
19991229 | 29218
19991230 | 29219
19991231 | 29220
20000101 | 29221
Code: Select all
dbadmin=> select min(date_key), max(date_key), min(rn), max(rn) from test_date;
min | max | min | max
----------+----------+-----+-------
19200101 | 20000101 | 1 | 29221
(1 row)
Code: Select all
dbadmin=> select date_key, rn from test_date where rn = (1 + randomint(29221));
date_key | rn
----------+-------
19790214 | 21595
(1 row)
Code: Select all
dbadmin=> select date_key, rn from test_date where rn = (1 + randomint(29221));
date_key | rn
----------+-------
19240302 | 1523
19900613 | 25732
(2 rows)
Code: Select all
dbadmin=> select date_key, rn from test_date where rn = (1 + randomint(29221));
date_key | rn
----------+----
(0 rows)
There are no duplicates:
Code: Select all
dbadmin=> select date_key, count(*) from test_date group by date_key having count(*) > 1;
date_key | count
----------+-------
(0 rows)
dbadmin=> select rn, count(*) from test_date group by rn having count(*) > 1;
rn | count
----+-------
(0 rows)
Thanks!