Бага или фича?

SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod                
PL/SQL Release 10.2.0.2.0 - Production                                          
CORE	10.2.0.2.0	Production                                                      
TNS for Linux: Version 10.2.0.2.0 - Production                                  
NLSRTL Version 10.2.0.2.0 - Production                                          

SQL> with src as (select 1 n from dual union
  2  select 2 from dual union
  3  select 3 from dual)
  4  select n from src
  5  where n=round(DBMS_RANDOM.VALUE(1,3));

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

         N                                                                      
----------                                                                      
         2                                                                      

SQL> /

         N                                                                      
----------                                                                      
         2                                                                      

SQL> /

         N                                                                      
----------                                                                      
         3                                                                      

SQL> /

         N                                                                      
----------                                                                      
         2                                                                      
         3                                                                      

SQL> /

         N                                                                      
----------                                                                      
         1                                                                      

SQL> /

         N                                                                      
----------                                                                      
         2  

 with src as (select 1 n from dual union
    select 2 from dual union
    select 3 from dual)
    select n, round(DBMS_RANDOM.VALUE(1,3))
    from src

SQL> explain plan for
  2  with src as
  3  (select 1 n from dual union
  4   select 2 from dual union
  5   select 3 from dual)
  6  select n
  7    from src
  8   where n=round(DBMS_RANDOM.VALUE(1,3));

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |       |       |       |
|   1 |  VIEW                 |             |       |       |       |
|   2 |   SORT UNIQUE         |             |       |       |       |
|   3 |    UNION-ALL          |             |       |       |       |
|*  4 |     FILTER            |             |       |       |       |
|   5 |      TABLE ACCESS FULL| DUAL        |       |       |       |
|*  6 |     FILTER            |             |       |       |       |
|   7 |      TABLE ACCESS FULL| DUAL        |       |       |       |
|*  8 |     FILTER            |             |       |       |       |
|   9 |      TABLE ACCESS FULL| DUAL        |       |       |       |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(1=ROUND("SYS"."DBMS_RANDOM"."VALUE"(1,3)))
   6 - filter(2=ROUND("SYS"."DBMS_RANDOM"."VALUE"(1,3)))
   8 - filter(3=ROUND("SYS"."DBMS_RANDOM"."VALUE"(1,3)))

Note: rule based optimization

24 строк выбрано.

SQL>

with src as 
(select 1 n from dual union
 select 2 from dual union
 select 3 from dual)
select n 
  from src
 where n = (SELECT round(DBMS_RANDOM.VALUE(1,3)) FROM DUAL);

Присоединен к:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Productio
SQL>
SQL>
SQL> exec DBMS_RANDOM.initialize(1)

Процедура PL/SQL успешно завершена.

SQL> with src as (select 1 n from dual union
  2   select 2 from dual union
  3   select 3 from dual)
  4   select n from src
  5   where n=round(DBMS_RANDOM.VALUE(1,3));

строки не выбраны

SQL> /

         N
----------
         1
         2
SQL> /

         N
----------
         2

SQL> /

         N
----------
         1
         2
SQL> /

строки не выбраны

SQL> /

строки не выбраны

SQL> /

         N
----------
         3

SQL> /

         N
----------
         2

SQL> /

строки не выбраны

SQL> /

         N
----------
         1

SQL>