[jira] [Commented] (SPARK-28379) Correlated scalar subqueries must be aggregated

2021-04-09 Thread Apache Spark (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-28379?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17318194#comment-17318194
 ] 

Apache Spark commented on SPARK-28379:
--

User 'allisonwang-db' has created a pull request for this issue:
https://github.com/apache/spark/pull/32111

> 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

[jira] [Commented] (SPARK-28379) Correlated scalar subqueries must be aggregated

2021-03-11 Thread Xiao Li (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-28379?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17299908#comment-17299908
 ] 

Xiao Li commented on SPARK-28379:
-

CC [~allisonwang-db] 

> 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

[jira] [Commented] (SPARK-28379) Correlated scalar subqueries must be aggregated

2021-03-11 Thread Dongjoon Hyun (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-28379?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17299646#comment-17299646
 ] 

Dongjoon Hyun commented on SPARK-28379:
---

This is reopened according to [~smilegator] 's suggestion on dev mailing list.

> 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