Bug view is not refreshed when replaced and then used in a for records in (select * from the_view)

set sql_mode=ORACLE;

create table if not exists DEMO_BUG

(

   AUTOID            bigint NOT NULL AUTO_INCREMENT UNIQUE KEY,

   Z_Field1          varchar2(30),

   Z_Field2          varchar2(30),

   Z_Field3          varchar2(30),

);

create table if not exists DEMO_LOG

(

   AUTOID           bigint NOT NULL AUTO_INCREMENT UNIQUE KEY,

   Z_INFO           varchar2(255)

)

INSERT INTO DEMO_BUG (Z_Field1, Z_Field2, Z_Field3) VALUES ('1', '1.1', '1.1.1');

INSERT INTO DEMO_BUG (Z_Field1, Z_Field2, Z_Field3) VALUES ('1', '1.1', '1.1.2');

INSERT INTO DEMO_BUG (Z_Field1, Z_Field2, Z_Field3) VALUES ('1', '1.1', '1.1.3');

INSERT INTO DEMO_BUG (Z_Field1, Z_Field2, Z_Field3) VALUES ('1', '1.2', '1.2.1');

INSERT INTO DEMO_BUG (Z_Field1, Z_Field2, Z_Field3) VALUES ('1', '1.2', '1.2.2');

INSERT INTO DEMO_BUG (Z_Field1, Z_Field2, Z_Field3) VALUES ('1', '1.2', '1.2.3');

delimiter $$

create or replace PROCEDURE MyDebug (v_info varchar)

AS

BEGIN

  INSERT INTO DEMO_LOG (Z_INFO) VALUES (v_info)

  ;

end;

$$

delimiter $$

create or replace PROCEDURE DEMO_BUG_ON_VIEW ()

as

   i number(2);

   nb number(2);

   sqlstring varchar(255);

begin

   call MyDebug('DEMO_BUG_ON_VIEW');

   truncate table DEMO_LOG;

   SELECT count(*) into @nb from ( select DISTINCT Z_Field1 from DEMO_BUG );

   call MyDebug(' nb records if group by field 1 = ' || @nb);

   SELECT count(*) into @nb from ( select DISTINCT Z_Field2 from DEMO_BUG );

   call MyDebug(' nb records if group by field 2 = ' || @nb);

   SELECT count(*) into @nb from ( select DISTINCT Z_Field3 from DEMO_BUG );

   call MyDebug(' nb records if group by field 3 = ' || @nb);

   --

   set @i:=1;

   REPEAT

      sqlstring:='create or replace view DEMO_VIEW_WITH_BUG as select DISTINCT Z_Field' || @i||' from DEMO_BUG';

      call MyDebug(sqlstring);

      prepare stmt from sqlstring;

      execute stmt;

      set @nb:=-1;

      select count(*) into @nb from DEMO_VIEW_WITH_BUG;

      call MyDebug(' nb records by view on field ' || @i || '  = ' || @nb);

      deallocate prepare stmt;

      SET @i = @i + 1; 

   UNTIL @i > 3

   END REPEAT;

end;

$$

--------------------------------------------------

SELECT * FROM DEMO_BUG;

---------------------------------------------------

AUTOID;Z_Field1;Z_Field2;Z_Field3

1     ;1       ;1.1     ;1.1.1

2     ;1       ;1.1     ;1.1.2

3     ;1       ;1.1     ;1.1.3

4     ;1       ;1.2     ;1.2.1

5     ;1       ;1.2     ;1.2.2

6     ;1       ;1.2     ;1.2.3

--------------------------------------------------

delimiter $$

begin

call DEMO_BUG_ON_VIEW;

SELECT * FROM DEMO_LOG;

END;

$$

--------------------------------------------------

AUTOID;Z_INFO

1; nb records if group by field 1 = 1

2; nb records if group by field 2 = 2

3; nb records if group by field 3 = 6

4;create or replace view DEMO_VIEW_WITH_BUG as select DISTINCT Z_Field1 from DEMO_BUG

5; nb records by view on field 1  = 1

6;create or replace view DEMO_VIEW_WITH_BUG as select DISTINCT Z_Field2 from DEMO_BUG

7; nb records by view on field 2  = 1                                                        <--------------------- BIG BUG IT SHOULD HAVE BEEN 2

8;create or replace view DEMO_VIEW_WITH_BUG as select DISTINCT Z_Field3 from DEMO_BUG

9; nb records by view on field 3  = 1                                                        <--------------------- BIG BUG IT SHOULD HAVE BEEN 6

--------------------------------------------------