A restriction of EXISTS subquery.

[thoshiai]

I found one problem of exists subuquery. IVM must need key column in target list when EXISTS clause is used. But currently we can't check it.

e.g.

CREATE TABLE mv_base_a (i int, j int);
INSERT INTO mv_base_a VALUES  (1,10),  (2,20);
CREATE TABLE mv_base_b (i int, k int);
INSERT INTO mv_base_b VALUES (1,101), (2,102);

BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_exists_subquery AS
  SELECT a.i FROM mv_base_a a
  WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i AND a.j + 90 < b.k);

SELECT *,  __ivm_exists_count_0__ FROM mv_ivm_exists_subquery ORDER BY i;
SELECT a.i FROM mv_base_a a
WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i AND a.j + 90 < b.k);

INSERT INTO mv_base_a VALUES(1,11);
INSERT INTO mv_base_b VALUES(1,102);

SELECT *, __ivm_exists_count_0__ FROM mv_ivm_exists_subquery ORDER BY i;
SELECT a.i FROM mv_base_a a
WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i AND a.j + 90 < b.k);

result is:

BEGIN
SELECT 1
 i | __ivm_exists_count_0__ 
---+------------------------
 1 |                      1
(1 row)

 i 
---
 1
(1 row)

INSERT 0 1
INSERT 0 1
 i | __ivm_exists_count_0__ 
---+------------------------
 1 |                      2
(1 row)

 i 
---
 1
 1
(2 rows)

[yugo-n]

@thoshiai can we close this issue?

[thoshiai]

Sure, I fortgot cloded it.