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: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org