Apache Cloudberry version

main

What happened

orca result error

What you think should happen instead

No response

How to reproduce

CREATE TABLE count_empty (a int, b int);
CREATE TABLE count_one (a int, b int);
INSERT INTO count_one VALUES (0, 0);

-- optimizer=off：
SET optimizer=off;
SELECT *
FROM count_one
WHERE count_one.a IN (
SELECT COUNT(*)
FROM count_empty
WHERE count_one.b = count_empty.b
);
a | b
---+---
0 | 0
(1 row)

-- optimizer=on：
SET optimizer=on;
SELECT *
FROM count_one
WHERE count_one.a IN (
SELECT COUNT(*)
FROM count_empty
WHERE count_one.b = count_empty.b
);
a | b
---+---
(0 rows)

Operating System

centos 9

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.