morvenhuang created SPARK-38707:
-----------------------------------
Summary: Allow user to insert into only certain columns of a table
Key: SPARK-38707
URL: https://issues.apache.org/jira/browse/SPARK-38707
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 3.2.1
Reporter: morvenhuang
When running INSERT INTO statement, it's quite common that user wants to insert
only certain columns, especially when the rest columns have default value.
Currently, spark allows user to specify column list for INSERT INTO statement
only when the column list contains all columns of the table.
Say we have a MySQL table:
{code:java}
CREATE TABLE t1(c1 int(11), c2 int(11)){code}
This INSERT INTO statement works in Spark,
{code:java}
INSERT INTO t1(c1, c2) values(1, 1){code}
While this ends up with exception,
{code:java}
INSERT INTO t1(c1) values(1){code}
{code:java}
org.apache.spark.sql.AnalysisException: unknown requires that the data to be
inserted have the same number of columns as the target table: target table has
2 column(s) but the inserted data has 1 column(s), including 0 partition
column(s) having constant value(s).
at
org.apache.spark.sql.errors.QueryCompilationErrors$.mismatchedInsertedDataColumnNumberError(QueryCompilationErrors.scala:1261)
at
org.apache.spark.sql.execution.datasources.PreprocessTableInsertion$.org$apache$spark$sql$execution$datasources$PreprocessTableInsertion$$preprocess(rules.scala:389)
at
org.apache.spark.sql.execution.datasources.PreprocessTableInsertion$$anonfun$apply$3.applyOrElse(rules.scala:429)
at
org.apache.spark.sql.execution.datasources.PreprocessTableInsertion$$anonfun$apply$3.applyOrElse(rules.scala:420)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.$anonfun$resolveOperatorsDownWithPruning$2(AnalysisHelper.scala:170)
at
org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:83)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.$anonfun$resolveOperatorsDownWithPruning$1(AnalysisHelper.scala:170)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:323)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsDownWithPruning(AnalysisHelper.scala:168)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsDownWithPruning$(AnalysisHelper.scala:164)
at
org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperatorsDownWithPruning(LogicalPlan.scala:30)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsWithPruning(AnalysisHelper.scala:99)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsWithPruning$(AnalysisHelper.scala:96)
at
org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperatorsWithPruning(LogicalPlan.scala:30)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperators(AnalysisHelper.scala:76)
at
org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperators$(AnalysisHelper.scala:75)
at
org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperators(LogicalPlan.scala:30)
at
org.apache.spark.sql.execution.datasources.PreprocessTableInsertion$.apply(rules.scala:420)
at
org.apache.spark.sql.execution.datasources.PreprocessTableInsertion$.apply(rules.scala:370)
......{code}
I can provide a fix for this.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
