EXISTS doesn't really work.

[lmwnshn]

Bug Report

EXISTS doesn't really work.

NoisePage

noisepage=# create table foo (a int); create table bar (b int);
CREATE TABLE
CREATE TABLE
noisepage=# insert into foo values (1),(2),(3); insert into bar values (1),(2);
INSERT 0 3
INSERT 0 2
noisepage=# select * from foo where exists (select * from bar);
 a 
---
 1
 1
 2
 2
 3
 3
(6 rows)

Postgres

postgres=# create table foo (a int); create table bar (b int);
CREATE TABLE
CREATE TABLE
postgres=# insert into foo values (1),(2),(3); insert into bar values (1),(2);
INSERT 0 3
INSERT 0 2
postgres=# select * from foo where exists (select * from bar);
 a 
---
 1
 2
 3
(3 rows)

---

I thought that I could (hackily) add NOT EXISTS based on existing EXISTS logic, setting IS_NULL instead of IS_NOT_NULL and pulling up a child, but it turns out that existing EXISTS logic doesn't work and that I should have verified this earlier.

[liyichao]

the result plan is a logicalmarkjoin, the first child with a IS_NOT_NULL(subquery.b) where clause, maybe this can be solved by insert a limit 1 into the subquery, what do you think @lmwnshn ?