[SPARK-26519] spark sql CHANGE COLUMN not working

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.1.0
• Component/s: SQL

Environment

• Assignee: Unassigned
• Reporter: suman gorantla
• Created: 02/Jan/19 20:26
• Updated: 12/Dec/22 18:10
• Resolved: 03/Jan/19 08:40

Description

Dear Team,

with spark sql I am unable to change the newly added column() position after an existing column in the table (old_column) of a hive external table please see the screenshot as in below 

scala> sql("ALTER TABLE enterprisedatalakedev.tmptst ADD COLUMNs (new_col  STRING)")
res14: org.apache.spark.sql.DataFrame = []

 sql("ALTER TABLE enterprisedatalakedev.tmptst CHANGE COLUMN new_col new_col  STRING AFTER old_col ")
org.apache.spark.sql.catalyst.parser.ParseException:
Operation not allowed: ALTER TABLE table [PARTITION partition_spec] CHANGE COLUMN ... FIRST | AFTER otherCol(line 1, pos 0)

== SQL ==
ALTER TABLE enterprisedatalakedev.tmptst CHANGE COLUMN new_col  new_col  STRING AFTER  old_col 
^^^

at org.apache.spark.sql.catalyst.parser.ParserUtils$.operationNotAllowed(ParserUtils.scala:39)
at org.apache.spark.sql.execution.SparkSqlAstBuilder$$anonfun$visitChangeColumn$1.apply(SparkSqlParser.scala:934)
at org.apache.spark.sql.execution.SparkSqlAstBuilder$$anonfun$visitChangeColumn$1.apply(SparkSqlParser.scala:928)
at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:99)
at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitChangeColumn(SparkSqlParser.scala:928)
at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitChangeColumn(SparkSqlParser.scala:55)
at org.apache.spark.sql.catalyst.parser.SqlBaseParser$ChangeColumnContext.accept(SqlBaseParser.java:1485)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:42)
at org.apache.spark.sql.catalyst.parser.AstBuilder$$anonfun$visitSingleStatement$1.apply(AstBuilder.scala:71)
at org.apache.spark.sql.catalyst.parser.AstBuilder$$anonfun$visitSingleStatement$1.apply(AstBuilder.scala:71)
at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:99)
at org.apache.spark.sql.catalyst.parser.AstBuilder.visitSingleStatement(AstBuilder.scala:70)
at org.apache.spark.sql.catalyst.parser.AbstractSqlParser$$anonfun$parsePlan$1.apply(ParseDriver.scala:69)
at org.apache.spark.sql.catalyst.parser.AbstractSqlParser$$anonfun$parsePlan$1.apply(ParseDriver.scala:68)
at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:97)
at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:48)
at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:68)
at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:623)
... 48 elided

 

Issue Links

• relates to: SPARK-23890 Support DDL for adding nested columns to struct types
• relates to: SPARK-24602 In Spark SQL, ALTER TABLE--CHANGE column1 column2 datatype is not supported in 2.3.1