title: Array Intersection in Spark SQL
body: <p>I have a table with a array type column named <code>writer</code> which has the values like <code>array[value1, value2]</code>, <code>array[value2, value3]</code>.... etc. </p>
  
  <p>I am doing <code>self join</code> to get results which have common values between arrays. I tried:</p>
  
  <pre><code>sqlContext.sql("SELECT R2.writer FROM table R1 JOIN table R2 ON R1.id != R2.id WHERE ARRAY_INTERSECTION(R1.writer, R2.writer)[0] is not null ")
  </code></pre>
  
  <p>And</p>
  
  <pre><code>sqlContext.sql("SELECT R2.writer FROM table R1 JOIN table R2 ON R1.id != R2.id WHERE ARRAY_INTERSECT(R1.writer, R2.writer)[0] is not null ")
  </code></pre>
  
  <p>But got same exception:</p>
  
  <blockquote>
    <p>Exception in thread "main" org.apache.spark.sql.AnalysisException:
    Undefined function: 'ARRAY_INTERSECT'. This function is neither a
    registered temporary function nor a permanent function registered in
    the database 'default'.; line 1 pos 80</p>
  </blockquote>
  
  <p>Probably Spark SQL does not support <code>ARRAY_INTERSECTION</code> and <code>ARRAY_INTERSECT</code>. How can I achieve my goal in <code>Spark SQL</code>? </p>