[SPARK-32431] The .schema() API behaves incorrectly for nested schemas that have column duplicates in case-insensitive mode

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.4.6, 3.0.0
• Fix Version/s: 3.1.0
• Component/s: SQL
• Assignee: Max Gekk
• Reporter: Michał Świtakowski
• Created: 24/Jul/20 15:20
• Updated: 31/Jul/20 12:12
• Resolved: 30/Jul/20 06:06

Description

The code below throws org.apache.spark.sql.AnalysisException: Found duplicate column(s) in the data schema: `camelcase`; for multiple file formats due to a duplicate column in the requested schema.

import org.apache.spark.sql.types._
spark.conf.set("spark.sql.caseSensitive", "false")
val formats = Seq("parquet", "orc", "avro", "json")
val caseInsensitiveSchema = new StructType().add("LowerCase", LongType).add("camelcase", LongType).add("CamelCase", LongType)
formats.map{ format =>
    val path = s"/tmp/$format"
    spark
    .range(1L)
    .selectExpr("id AS lowercase", "id + 1 AS camelCase")
    .write.mode("overwrite").format(format).save(path) 
    spark.read.schema(caseInsensitiveSchema).format(format).load(path).show
}

Similar code with nested schema behaves inconsistently across file formats and sometimes returns incorrect results:

import org.apache.spark.sql.types._
spark.conf.set("spark.sql.caseSensitive", "false")
val formats = Seq("parquet", "orc", "avro", "json")
val caseInsensitiveSchema = new StructType().add("StructColumn", new StructType().add("LowerCase", LongType).add("camelcase", LongType).add("CamelCase", LongType))
formats.map{ format =>
    val path = s"/tmp/$format"
    spark
    .range(1L)
    .selectExpr("NAMED_STRUCT('lowercase', id, 'camelCase', id + 1) AS StructColumn")
    .write.mode("overwrite").format(format).save(path)
    
    spark.read.schema(caseInsensitiveSchema).format(format).load(path).show
}

The desired behavior likely should be returning an exception just like in the flat schema scenario.

Issue Links

• relates to: SPARK-32510 JDBC doesn't check duplicate column names in nested structures
• relates to: SPARK-20460 Make it more consistent to handle column name duplication
• links to: [Github] Pull Request #29234 (MaxGekk)