Filtering rows with "DBMS_RANDOM": a small bug? (tested in Oracle 10.2 and 11.1)
Hello,
I want to share something that has annoyed me. My goal was to filter "randomly" x% of a SELECT statement, and the result was not what I was expecting... I provide the code for a test:
CREATE TABLE mytable( myfield VARCHAR2(20) );
INSERT INTO mytable
SELECT TO_CHAR( TO_DATE( TO_CHAR( LEVEL ), 'J' ), 'Jsp' )
FROM dual
CONNECT BY LEVEL <= 25
;
COL yes_or_no FOR 0.000
SELECT yes_or_no, '10%? ' || myfield
FROM ( SELECT myfield
, dbms_random.value yes_or_no
FROM mytable
) x
WHERE x.yes_or_no <= 0.1
;
YES_OR_NO '10%?'||MYFIELD
--------- -------------------------
0.069 10%? Five
0.019 10%? Eight
/
YES_OR_NO '10%?'||MYFIELD
I want to share something that has annoyed me. My goal was to filter "randomly" x% of a SELECT statement, and the result was not what I was expecting... I provide the code for a test:
CREATE TABLE mytable( myfield VARCHAR2(20) );
INSERT INTO mytable
SELECT TO_CHAR( TO_DATE( TO_CHAR( LEVEL ), 'J' ), 'Jsp' )
FROM dual
CONNECT BY LEVEL <= 25
;
COL yes_or_no FOR 0.000
SELECT yes_or_no, '10%? ' || myfield
FROM ( SELECT myfield
, dbms_random.value yes_or_no
FROM mytable
) x
WHERE x.yes_or_no <= 0.1
;
YES_OR_NO '10%?'||MYFIELD
--------- -------------------------
0.069 10%? Five
0.019 10%? Eight
/
YES_OR_NO '10%?'||MYFIELD
0