Bug 27782078 验证

Bug 27782078 : WRONG RESULT PUSHING PREDICATE INTO VIEW THAT IS A UNION ALL OF TWO TABLES WITH

Wrong result pushing predicate into view that is a union all of two tables.
Ex:

WITH s1 AS
      (SELECT /*+ materialize */
              id
       ,      tstamp                           AS tstamp1
       ,      CAST(tstamp + 1 AS TIMESTAMP) AS tstamp2
       FROM   t3
       WHERE  id = 42)
   SELECT count(*)
   FROM
   (  SELECT id
      ,            tstamp
      FROM   t1
      UNION ALL
      SELECT id
      ,            tstamp
      FROM   t2
   )         t
   ,         s1
   WHERE  t.id           = s1.id
   AND         t.tstamp >= s1.tstamp1
   AND         t.tstamp <  s1.tstamp2;

Wrong result is caused by a filter predicate which
incorrectly has its operator flipped.

filter(“S1”.“TSTAMP2”<“S1”.“TSTAMP1”)

This condition is never satisfied and generates zero rows for the query

REDISCOVERY INFORMATION:

1. If you encounter wrong results in a query that tries to push predicates
   into a view that is the union all of two tables

2. the filter you see generated in the explain plan has a condition which is
   incorrect (operator is flipped and result should never be true)

验证过程

WITH s1 AS
      (SELECT /*+ materialize */
              id
       ,      tstamp                        AS tstamp1
       ,      CAST(tstamp + 1 AS TIMESTAMP) AS tstamp2
       FROM   t3
       WHERE  id = 42)
   SELECT count(*)
   FROM
   (  SELECT id
      ,            tstamp
      FROM   t1
      UNION ALL
      SELECT id
      ,            tstamp
      FROM   t2
   )         t
   ,         s1
   WHERE  t.id           = s1.id
   AND         t.tstamp >= s1.tstamp1
   AND         t.tstamp <  s1.tstamp2;

–创建表 T1-T3

 CREATE TABLE T3 
(
  ID INT 
, TSTAMP TIMESTAMP(9) 
);

–插入数据

declare
i number:=1;
begin
for i in 1..100 loop
   insert into t3 (id,tstamp) values(i,SYSTIMESTAMP);
   insert into t1 (id,tstamp) values(i,SYSTIMESTAMP);
   insert into t2 (id,tstamp) values(i,SYSTIMESTAMP);
end loop;
commit;
end;

结果正常，版本换了12.2.0.1 和19.8.0.0 均正常显示结果
也许是偶发性bug

– 删除表

drop table t1;
drop table t2;
drop table t3;