[SPARK-28379] Correlated scalar subqueries must be aggregated

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.2.0
• Component/s: SQL
• Target Version/s: 3.2.0
• Assignee: Allison Wang
• Reporter: Yuming Wang
• Created: 13/Jul/19 16:22
• Updated: 13/Apr/21 07:28
• Resolved: 13/Apr/21 07:27

Description

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;

PostgreSQL:

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)

Spark SQL:

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]
;;

Issue Links

• links to: [Github] Pull Request #32111 (allisonwang-db)