Unexpected join results

[Олег Владимиров]

Hi, I joined table with sequense numbers whith one-row table. I expect one row in result set. But in some cases there are two or three rows in result set. Isn't it a bug?

-- CREATE TABLE AND ADD NUMBERS FROM 1 TO 100
drop table if exists test;
create table test (nbr int);

with cte_seq (nbr) as
(
   select 1 nbr from dual
   union all
   select t.nbr + 1 nbr from cte_seq t where t.nbr < 100

)
insert into test (nbr)
select t.nbr from cte_seq t;


-- JOIN TABLE WITH NUMBERS WITH ONE-ROW TABLE
select t.nbr, m.rnd
from test t
    join (select cast(ceil(random() * 100) as int) rnd from dual) m
       on m.rnd = t.nbr
;

/* RESULTS:
NBR | RND
----+----
  5 |   5
 23 |  23
 39 |  39
*/

[Evgenij Ryazanov]

Hello.

No, it doesn't look like a bug. Your subquery contains a non-deterministic expression, so it cannot be materialized and may be evaluated multiple times during execution of this query depending on execution plan, so the result of the whole query is undefined. You need to rewrite it somehow.