Github user squito commented on a diff in the pull request: https://github.com/apache/spark/pull/18853#discussion_r140797299 --- Diff: sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala --- @@ -2677,4 +2677,142 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { checkAnswer(df, Row(1, 1, 1)) } } + + test("SPARK-21646: CommonTypeForBinaryComparison: StringType vs NumericType") { + withTempView("v") { + val str1 = Long.MaxValue.toString + "1" + val str2 = Int.MaxValue.toString + "1" + val str3 = "10" + Seq(str1, str2, str3).toDF("c1").createOrReplaceTempView("v") + withSQLConf(SQLConf.typeCoercionMode.key -> "hive") { + checkAnswer(sql("SELECT c1 from v where c1 > 0"), + Row(str1) :: Row(str2) :: Row(str3) :: Nil) + checkAnswer(sql("SELECT c1 from v where c1 > 0L"), + Row(str1) :: Row(str2) :: Row(str3) :: Nil) + } + + withSQLConf(SQLConf.typeCoercionMode.key -> "default") { + checkAnswer(sql("SELECT c1 from v where c1 > 0"), Row(str3) :: Nil) + checkAnswer(sql("SELECT c1 from v where c1 > 0L"), Row(str2) :: Row(str3) :: Nil) + } + } + } + + test("SPARK-21646: CommonTypeForBinaryComparison: DoubleType vs IntegerType") { + withTempView("v") { + Seq(("0", 1), ("-0.4", 2), ("0.6", 3)).toDF("c1", "c2").createOrReplaceTempView("v") + withSQLConf(SQLConf.typeCoercionMode.key -> "hive") { + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = 0"), Seq(Row("0"))) + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = 0L"), Seq(Row("0"))) + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = 0.0"), Seq(Row("0"))) + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = -0.4"), Seq(Row("-0.4"))) + checkAnswer(sql("SELECT count(*) FROM v WHERE c1 > 0"), Row(1) :: Nil) + } + + withSQLConf(SQLConf.typeCoercionMode.key -> "default") { + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = 0"), Seq(Row("0"), Row("-0.4"), Row("0.6"))) + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = 0L"), Seq(Row("0"), Row("-0.4"), Row("0.6"))) + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = 0.0"), Seq(Row("0"))) + checkAnswer(sql("SELECT c1 FROM v WHERE c1 = -0.4"), Seq(Row("-0.4"))) + checkAnswer(sql("SELECT count(*) FROM v WHERE c1 > 0"), Row(0) :: Nil) + } + } + } + + test("SPARK-21646: CommonTypeForBinaryComparison: StringType vs DateType") { + withTempView("v") { + val v1 = Date.valueOf("2017-09-22") + val v2 = Date.valueOf("2017-09-09") + Seq(v1, v2).toDF("c1").createTempView("v") + withSQLConf(SQLConf.typeCoercionMode.key -> "hive") { + checkAnswer(sql("select c1 from v where c1 > '2017-8-1'"), Row(v1) :: Row(v2) :: Nil) + checkAnswer(sql("select c1 from v where c1 > cast('2017-8-1' as date)"), + Row(v1) :: Row(v2) :: Nil) + } + + withSQLConf(SQLConf.typeCoercionMode.key -> "default") { + checkAnswer(sql("select c1 from v where c1 > '2017-8-1'"), Nil) + checkAnswer(sql("select c1 from v where c1 > cast('2017-8-1' as date)"), + Row(v1) :: Row(v2) :: Nil) + } + } + } + + test("SPARK-21646: CommonTypeForBinaryComparison: StringType vs TimestampType") { + withTempView("v") { + val v1 = Timestamp.valueOf("2017-07-21 23:42:12.123") + val v2 = Timestamp.valueOf("2017-08-21 23:42:12.123") + Seq(v1, v2).toDF("c1").createTempView("v") + withSQLConf(SQLConf.typeCoercionMode.key -> "hive") { + checkAnswer(sql("select c1 from v where c1 > '2017-8-1'"), Row(v2) :: Nil) + checkAnswer(sql("select c1 from v where c1 > cast('2017-8-1' as timestamp)"), + Row(v2) :: Nil) + } + + withSQLConf(SQLConf.typeCoercionMode.key -> "default") { + checkAnswer(sql("select c1 from v where c1 > '2017-8-1'"), Nil) + checkAnswer(sql("select c1 from v where c1 > cast('2017-8-1' as timestamp)"), + Row(v2) :: Nil) --- End diff -- I think we should include a comparison which is only the year, eg. ` > '2014'`, as that was listed as the motivation for the "default" behavior in the code comments.
--- --------------------------------------------------------------------- To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org