[SPARK-24864] Cannot resolve auto-generated column ordinals in a hive view

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 2.0.1, 2.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Abhishek Madav
• Created: 19/Jul/18 21:22
• Updated: 28/Oct/18 05:29
• Resolved: 20/Jul/18 18:50

Description

Spark job reading from a hive-view fails with analysis exception when resolving column ordinals which are autogenerated.

Exception:

scala> spark.sql("Select * from vsrc1new").show
org.apache.spark.sql.AnalysisException: cannot resolve '`vsrc1new._c1`' given input columns: [id, upper(name)]; line 1 pos 24;
'Project [*]
+- 'SubqueryAlias vsrc1new, `default`.`vsrc1new`
   +- 'Project [id#634, 'vsrc1new._c1 AS uname#633]
      +- SubqueryAlias vsrc1new
         +- Project [id#634, upper(name#635) AS upper(name)#636]
            +- MetastoreRelation default, src1

  at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
  at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1$$anonfun$apply$2.applyOrElse(CheckAnalysis.scala:77)
  at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1$$anonfun$apply$2.applyOrElse(CheckAnalysis.scala:74)
  at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:310)
  at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:310)
  at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:70)
  at org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:309)

Steps to reproduce:

1: Create a simple table, say src

CREATE TABLE `src1`(`id` int,  `name` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

2: Create a view, say with name vsrc1new

CREATE VIEW vsrc1new AS SELECT id, `_c1` AS uname FROM (SELECT id, upper(name) FROM src1) vsrc1new;

3. Selecting data from this view in hive-cli/beeline doesn't cause any error.

4. Creating a dataframe using:

spark.sql("Select * from vsrc1new").show //throws error

The auto-generated column names for the view are not resolved. Am I possibly missing some spark-sql configuration here? I tried the repro-case against spark 1.6 and that worked fine. Any inputs are appreciated.

Issue Links

• is duplicated by: SPARK-25833 Update migration guide for Hive view compatibility