Spark SQL Correlated column is not allowed in predicate

seou1 0
Viewed 62

I have to join 2 tables in Spark such that the value of a column in first table is within the range of the columns in second table. There is no joining column between the two table hence I can not use the normal join SQL. I am using the below query:

select t.*, (select MAX(p.grade) from table1 p where 
p.marks_lower_bound <= ROUND(t.marks) and 
p.marks_upper_bound >= ROUND(t.marks)) from table2 t;

So based on the marks in table2 I want to find the grade that is stored in table1 with the marks ranges. I am receiving the below error:

AnalysisException: Correlated column is not allowed in predicate

Any idea how to resolve this is Spark SQL? I have tried this query in MySQL and it is working fine there, but it is failing in Spark SQL. Note that table1 and table2 are the temp tables created from other Spark APIs. Thanks.

correlated-subquery mysql apache-spark apache-spark-sql sql
1 Answers

Correlated columns are not allowed with non-equality predicates ->SPARK-36114.

You can use "normal" join though. Just note that you can't have * in group by, so you need to provide column list explicitly. Or maybe you don't need grouping at all (if ranges are not overlapping).

select t.col1, t.col2, max(p.grade)
  from table2 t
  left join table1 p on (p.marks_lower_bound <= round(t.marks)
                     and p.marks_upper_bound >= round(t.marks))
 group by t.col1, t.col2;
Kombajn zbożowy0
Related