Github user squito commented on a diff in the pull request:
https://github.com/apache/spark/pull/18853#discussion_r140801036
--- 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 --
perhaps there should also be a comparison for a time with more degrees of
precision? It seems from the original discussion in
https://issues.apache.org/jira/browse/SPARK-8420?focusedCommentId=14592654&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14592654
this was one of the concerns, eg. with `'1969-12-31 16:00:00'` `'1969-12-31
16:00:00.0'`
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
