[SPARK-20761] Union uses column order rather than schema

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 2.1.1
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Nakul Jeirath
• Created: 16/May/17 05:48
• Updated: 12/Dec/22 18:11
• Resolved: 16/May/17 14:08

Description

I believe there is an issue when using union to combine two dataframes when the order of columns differ between the left and right side of the union:

import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.types.{BooleanType, StringType, StructField, StructType}

val schema = StructType(Seq(
  StructField("id", StringType, false),
  StructField("flag_one", BooleanType, false),
  StructField("flag_two", BooleanType, false),
  StructField("flag_three", BooleanType, false)
))

val rowRdd = spark.sparkContext.parallelize(Seq(
  Row("1", true, false, false),
  Row("2", false, true, false),
  Row("3", false, false, true)
))

spark.createDataFrame(rowRdd, schema).createOrReplaceTempView("temp_flags")

val emptyData = spark.createDataFrame(spark.sparkContext.emptyRDD[Row], schema)

//Select columns out of order with respect to the emptyData schema
val data = emptyData.union(spark.sql("select id, flag_two, flag_three, flag_one from temp_flags"))

Selecting the data from the "temp_flags" table results in:

spark.sql("select * from temp_flags").show
+---+--------+--------+----------+
| id|flag_one|flag_two|flag_three|
+---+--------+--------+----------+
|  1|    true|   false|     false|
|  2|   false|    true|     false|
|  3|   false|   false|      true|
+---+--------+--------+----------+

Which is the data we'd expect but when inspecting "data" we get:

data.show()
+---+--------+--------+----------+
| id|flag_one|flag_two|flag_three|
+---+--------+--------+----------+
|  1|   false|   false|      true|
|  2|    true|   false|     false|
|  3|   false|    true|     false|
+---+--------+--------+----------+

Having a non-empty dataframe on the left side of the union doesn't seem to make a difference either:

spark.sql("select * from temp_flags").union(spark.sql("select id, flag_two, flag_three, flag_one from temp_flags")).show
+---+--------+--------+----------+
| id|flag_one|flag_two|flag_three|
+---+--------+--------+----------+
|  1|    true|   false|     false|
|  2|   false|    true|     false|
|  3|   false|   false|      true|
|  1|   false|   false|      true|
|  2|    true|   false|     false|
|  3|   false|    true|     false|
+---+--------+--------+----------+

Issue Links

• duplicates: SPARK-15918 unionAll returns wrong result when two dataframes has schema in different order
• relates to: SPARK-22335 Union for DataSet uses column order instead of types for union