[SPARK-42909] INSERT INTO with column list does not work

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 3.3.2
• Component/s: SQL
• Labels: databricks documentation spark-sql sql

Environment

Databricks DBR12.2 on AZure, running Spark 3.3.2

Documentation: [INSERT - Azure Databricks - Databricks SQL | Microsoft Learn|https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-dml-insert-into]

• Assignee: Unassigned
• Reporter: Tjomme Vergauwen
• Created: 23/Mar/23 12:32
• Updated: 24/Mar/23 01:54
• Resolved: 24/Mar/23 01:54

Description

Hi,

When performing a INSERT INTO with a defined incomplete column list, the missing columns should get a NULL value. However, an error is thrown indicating that the column is missing.

Case simulation:

drop table if exists default.TVTest;
create table default.TVTest
( col1 int NOT NULL
, col2 int
);
insert into default.TVTest select 1,2;
insert into default.TVTest select 2,NULL; --> col2 can contain NULL values
insert into default.TVTest (col1) select 3; – Error in SQL statement: DeltaAnalysisException: Column col2 is not specified in INSERT
insert into default.TVTest (col1) VALUES (3); – Error in SQL statement: DeltaAnalysisException: Column col2 is not specified in INSERT
select * from default.TVTest;

Issue Links

• duplicates: SPARK-42521 Add NULL values for INSERT commands with user-specified lists of fewer columns than the target table