Materialized view contains wrong result

Question

I'm having trouble with the content of a materialized view in Oracle 19c (version 19.15). I've managed to distill the issues into a reproducible test with this script:

create table b(
    tsn varchar2(16) not null primary key,
    fid varchar2(256) not null
);

create table bs(
    tsn varchar2(16) not null constraint bet_stakes_fk references b,
    leg number(1) not null,
    amount number(10) not null,
    primary key (tsn, leg) using index compress 1
);

create materialized view log on b
    with primary key, rowid, sequence, commit scn (fid)
    including new values;

create materialized view log on bs
    with primary key, rowid, sequence, commit scn (amount)
    including new values;

create materialized view bsd_mv
refresh fast start with (sysdate - 1) next (sysdate + 1/14400)
as
select fid, leg, sum(amount), count(*)
from bs inner join b using (tsn)
group by fid, leg;

insert into b values ('a', 'o');
insert into bs values ('a', 1, 10);
commit;

delete from bs where tsn = 'a';
delete from b where tsn = 'a';
insert into b values ('a', 'o');
insert into bs values ('a', 1, 5);
commit;

Wait 10 seconds or so before selecting

select * from bsd_mv;

The result will vary somewhat with different runs of the script, but usually the result will be

| Fid | Leg | Sum | Count |
| --- | --- | --- | ----- |
| o   |   1 |  15 |     3 |

... where I would expect it to be...

| Fid | Leg | Sum | Count |
| --- | --- | --- | ----- |
| o   |   1 |   5 |     1 |

If I run the query the view is based on, I always get the expected result.

Am I missing something in the setup, or do I have the wrong expectations, or have I triggered a bug in Oracle?

Materialized view contains wrong result

0 Answers