[
https://issues.apache.org/jira/browse/SPARK-28379?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Apache Spark reassigned SPARK-28379:
------------------------------------
Assignee: (was: Apache Spark)
> Correlated scalar subqueries must be aggregated
> -----------------------------------------------
>
> Key: SPARK-28379
> URL: https://issues.apache.org/jira/browse/SPARK-28379
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 3.0.0
> Reporter: Yuming Wang
> Priority: Major
>
> {code:sql}
> create or replace temporary view INT8_TBL as select * from
> (values
> (123, 456),
> (123, 4567890123456789),
> (4567890123456789, 123),
> (4567890123456789, 4567890123456789),
> (4567890123456789, -4567890123456789))
> as v(q1, q2);
> select * from
> int8_tbl t1 left join
> (select q1 as x, 42 as y from int8_tbl t2) ss
> on t1.q2 = ss.x
> where
> 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
> order by 1,2;
> {code}
> PostgreSQL:
> {noformat}
> postgres=# select * from
> postgres-# int8_tbl t1 left join
> postgres-# (select q1 as x, 42 as y from int8_tbl t2) ss
> postgres-# on t1.q2 = ss.x
> postgres-# where
> postgres-# 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
> postgres-# order by 1,2;
> q1 | q2 | x | y
> ------------------+------------------+------------------+----
> 123 | 4567890123456789 | 4567890123456789 | 42
> 123 | 4567890123456789 | 4567890123456789 | 42
> 123 | 4567890123456789 | 4567890123456789 | 42
> 4567890123456789 | 123 | 123 | 42
> 4567890123456789 | 123 | 123 | 42
> 4567890123456789 | 4567890123456789 | 4567890123456789 | 42
> 4567890123456789 | 4567890123456789 | 4567890123456789 | 42
> 4567890123456789 | 4567890123456789 | 4567890123456789 | 42
> (8 rows)
> {noformat}
> Spark SQL:
> {noformat}
> spark-sql> select * from
> > int8_tbl t1 left join
> > (select q1 as x, 42 as y from int8_tbl t2) ss
> > on t1.q2 = ss.x
> > where
> > 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
> > order by 1,2;
> Error in query: Correlated scalar subqueries must be aggregated: GlobalLimit 1
> +- LocalLimit 1
> +- Project [1 AS 1#169]
> +- Filter isnotnull(outer(y#167))
> +- SubqueryAlias `t3`
> +- SubqueryAlias `int8_tbl`
> +- Project [q1#164L, q2#165L]
> +- Project [col1#162L AS q1#164L, col2#163L AS q2#165L]
> +- SubqueryAlias `v`
> +- LocalRelation [col1#162L, col2#163L]
> ;;
> {noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
