[ https://issues.apache.org/jira/browse/SPARK-45507?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Josh Rosen updated SPARK-45507: ------------------------------- Labels: correctness pull-request-available (was: pull-request-available) > Correctness bug in correlated scalar subqueries with COUNT aggregates > --------------------------------------------------------------------- > > Key: SPARK-45507 > URL: https://issues.apache.org/jira/browse/SPARK-45507 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 4.0.0 > Reporter: Andy Lam > Assignee: Andy Lam > Priority: Major > Labels: correctness, pull-request-available > Fix For: 4.0.0 > > > {code:java} > > create view if not exists t1(a1, a2) as values (0, 1), (1, 2); > create view if not exists t2(b1, b2) as values (0, 2), (0, 3); > create view if not exists t3(c1, c2) as values (0, 2), (0, 3); > -- Example 1 > select ( > select SUM(l.cnt + r.cnt) > from (select count(*) cnt from t2 where t1.a1 = t2.b1 having cnt = 0) l > join (select count(*) cnt from t3 where t1.a1 = t3.c1 having cnt = 0) r > on l.cnt = r.cnt > ) from t1 > -- Correct answer: (null, 0) > +----------------------+ > |scalarsubquery(c1, c1)| > +----------------------+ > |null | > |null | > +----------------------+ > -- Example 2 > select ( select sum(cnt) from (select count(*) cnt from t2 where t1.c1 = > t2.c1) ) from t1 > -- Correct answer: (2, 0) > +------------------+ > |scalarsubquery(c1)| > +------------------+ > |2 | > |null | > +------------------+ > -- Example 3 > select ( select count(*) from (select count(*) cnt from t2 where t1.c1 = > t2.c1) ) from t1 > -- Correct answer: (1, 1) > +------------------+ > |scalarsubquery(c1)| > +------------------+ > |1 | > |0 | > +------------------+ {code} > > > DB fiddle for correctness > check:[https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/10403#] -- This message was sent by Atlassian Jira (v8.20.10#820010) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org