sraoss / pg_ivm

IVM (Incremental View Maintenance) implementation as a PostgreSQL extension
Other
854 stars 24 forks source link

Views using EXISTS with tuple duplication doesn't work correctly #82

Closed yugo-n closed 4 months ago

yugo-n commented 4 months ago

Example:

test=# select get_immv_def('mv');
          get_immv_def           
---------------------------------
  SELECT i                      +
    FROM t1                     +
   WHERE (EXISTS ( SELECT 1     +
            FROM t2             +
           WHERE (t1.i = t2.i)))
(1 row)

test=# table t1;
 i 
---
 1
 1
 1
(3 rows)

test=# table t2;
 i 
---
(0 rows)

After inserting a row (1) into t2, the view has only one tuple.

test=# insert into t2 values (1);
INSERT 0 1
test=# table mv;
 i | __ivm_exists_count_0__ 
---+------------------------
 1 |                      1
(1 row)

It is not correct because it should have three duplicated tuples as followings.

test=# select * from t1 where exists (select 1 from t2 where t1.i=t2.i);
 i 
---
 1
 1
 1
(3 rows)