[Bug] "where exists" with "GROUP BY" is ignored

[shedar]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.0.2

What's Wrong?

Query select ... where exists(subquery) ignores the condition if subquery has "group by"

What You Expected?

"group by" and "having" to be supported in exists subquery

How to Reproduce?

create table t1 (
    id int
)
UNIQUE KEY (`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

create table t2 (
    id int,
    t1_id int
)
UNIQUE KEY (`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

insert into t1 (id) values (1),(2),(3),(4),(5),(6);
insert into t2 (id, t1_id) values (1,1),(2,1),(3,3),(4,5),(5,5),(6,5);

select t1.id from t1 inner join t2 on t1.id = t2.t1_id group by t1.id;
-- OK: returns 1,3,5 as expected
select distinct t1.id from t1 where exists(select 1 from t2 where t1.id = t2.t1_id);
-- OK: returns 1,3,5 as expected
select distinct t1.id from t1 where exists(select 1 from t2 where t1.id = t2.t1_id GROUP BY t2.t1_id);
-- BUG: returns 1,2,3,4,5,6 (all rows); expected 1,3,5

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

[LemonLiTree]

2.0.3 fixed this issue

[shedar]

@LemonLiTree thank you for the quick reply, tried with 2.0.3-rc05 - exists+having works as intended.
But exists behaviour still seems odd. Using the same tables:

select t1.id from t1
where
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id=5) and
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id=6);
-- OK: returns 5

-- now try to invert the condition
select t1.id from t1
where
    not (
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 5) and
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 6)
        );
-- BUG: no rows, expected 1,2,3,4,6

-- if I open parentheses using de Morgans' laws, it works:
select t1.id from t1
where
    not exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 5) or
    not exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 6);
-- OK: 1,2,3,4,6

Should I create a separate issue for it?

[LemonLiTree]

@LemonLiTree thank you for the quick reply, tried with 2.0.3-rc05 - exists+having works as intended. But exists behaviour still seems odd. Using the same tables:

select t1.id from t1
where
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id=5) and
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id=6);
-- OK: returns 5

-- now try to invert the condition
select t1.id from t1
where
    not (
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 5) and
    exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 6)
        );
-- BUG: no rows, expected 1,2,3,4,6

-- if I open parentheses using de Morgans' laws, it works:
select t1.id from t1
where
    not exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 5) or
    not exists(select 1 from t2 where t1.id = t2.t1_id and t2.id = 6);
-- OK: 1,2,3,4,6

Should I create a separate issue for it?

No need, we will fix it

[LemonLiTree]

#28713