[SPARK-4226] SparkSQL - Add support for subqueries in predicates

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.2.0
• Fix Version/s: 2.0.0
• Component/s: SQL

Environment

Spark 1.2 snapshot

• Assignee: Herman van Hövell
• Reporter: Terry Siu
• Created: 04/Nov/14 19:49
• Updated: 22/Apr/16 21:35
• Resolved: 19/Apr/16 22:17

Description

I have a test table defined in Hive as follows:

CREATE TABLE sparkbug (
  id INT,
  event STRING
) STORED AS PARQUET;

and insert some sample data with ids 1, 2, 3.

In a Spark shell, I then create a HiveContext and then execute the following HQL to test out subquery predicates:

val hc = HiveContext(hc)
hc.hql("select customerid from sparkbug where customerid in (select customerid from sparkbug where customerid in (2,3))")

I get the following error:

java.lang.RuntimeException: Unsupported language features in query: select customerid from sparkbug where customerid in (select customerid from sparkbug where customerid in (2,3))
TOK_QUERY
  TOK_FROM
    TOK_TABREF
      TOK_TABNAME
        sparkbug
  TOK_INSERT
    TOK_DESTINATION
      TOK_DIR
        TOK_TMP_FILE
    TOK_SELECT
      TOK_SELEXPR
        TOK_TABLE_OR_COL
          customerid
    TOK_WHERE
      TOK_SUBQUERY_EXPR
        TOK_SUBQUERY_OP
          in
        TOK_QUERY
          TOK_FROM
            TOK_TABREF
              TOK_TABNAME
                sparkbug
          TOK_INSERT
            TOK_DESTINATION
              TOK_DIR
                TOK_TMP_FILE
            TOK_SELECT
              TOK_SELEXPR
                TOK_TABLE_OR_COL
                  customerid
            TOK_WHERE
              TOK_FUNCTION
                in
                TOK_TABLE_OR_COL
                  customerid
                2
                3
        TOK_TABLE_OR_COL
          customerid

scala.NotImplementedError: No parse rules for ASTNode type: 817, text: TOK_SUBQUERY_EXPR :
TOK_SUBQUERY_EXPR
  TOK_SUBQUERY_OP
    in
  TOK_QUERY
    TOK_FROM
      TOK_TABREF
        TOK_TABNAME
          sparkbug
    TOK_INSERT
      TOK_DESTINATION
        TOK_DIR
          TOK_TMP_FILE
      TOK_SELECT
        TOK_SELEXPR
          TOK_TABLE_OR_COL
            customerid
      TOK_WHERE
        TOK_FUNCTION
          in
          TOK_TABLE_OR_COL
            customerid
          2
          3
  TOK_TABLE_OR_COL
    customerid
" +
         
org.apache.spark.sql.hive.HiveQl$.nodeToExpr(HiveQl.scala:1098)
        
        at scala.sys.package$.error(package.scala:27)
        at org.apache.spark.sql.hive.HiveQl$.createPlan(HiveQl.scala:252)
        at org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:50)
        at org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:49)
        at scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:136)

This thread also brings up lack of subquery support in SparkSQL. It would be nice to have subquery predicate support in a near, future release (1.3, maybe?).

Issue Links

• duplicates: SPARK-13831 TPC-DS Query 35 fails with the following compile error
• duplicates: SPARK-12543 Support subquery in select/where/having
• duplicates: SPARK-12545 Support exists condition
• duplicates: SPARK-10600 SparkSQL - Support for Not Exists in a Correlated Subquery
• is related to: SPARK-10600 SparkSQL - Support for Not Exists in a Correlated Subquery
• relates to: SPARK-11041 Add (NOT) IN / EXISTS support for predicates
• links to: [Github] Pull Request #3249 (ravipesala)
• links to: [Github] Pull Request #3888 (saucam)
• links to: [Github] Pull Request #4812 (chenghao-intel)
• links to: [Github] Pull Request #9055 (chenghao-intel)
• links to: [Github] Pull Request #10706 (davies)
• links to: [Github] Pull Request #12306 (hvanhovell)