This is an automated email from the ASF dual-hosted git repository.
gurwls223 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 8c625eaaf646 [SPARK-48943][SQL][TESTS][FOLLOWUP] Fix the `h2` filter
push-down test case failure with ANSI mode off
8c625eaaf646 is described below
commit 8c625eaaf646166ad11ad7299b94c91286c913e6
Author: Wei Guo <[email protected]>
AuthorDate: Thu Jul 25 09:53:32 2024 +0900
[SPARK-48943][SQL][TESTS][FOLLOWUP] Fix the `h2` filter push-down test case
failure with ANSI mode off
### What changes were proposed in this pull request?
This PR aims to fix the `h2` filter push-down test case failure with ANSI
mode off.
### Why are the changes needed?
Fix test failure.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Manually test of the whole `JDBCV2Suite` with ANSI mode off and on.
1. Method One: with IDEA.
- ANSI mode off: with `SPARK_ANSI_SQL_MODE=false`
<img width="1066" alt="image"
src="https://github.com/user-attachments/assets/13ec8ff4-0699-4f3e-95c4-74f53d9824fe";>
- ANSI mode on: without `SPARK_ANSI_SQL_MODE` env variable
<img width="1066" alt="image"
src="https://github.com/user-attachments/assets/8434bf0c-b332-4663-965c-0d17d60da78a";>
2. Method Two: with commands.
- ANSI mode off
```
SPARK_ANSI_SQL_MODE=false
$ build/sbt
> project sql
> testOnly org.apache.spark.sql.jdbc.JDBCV2Suite
```
- ANSI mode on
```
UNSET SPARK_ANSI_SQL_MODE
$ build/sbt
> project sql
> testOnly org.apache.spark.sql.jdbc.JDBCV2Suite
```
Test results:
1. The issue on current `master` branch
- with `SPARK_ANSI_SQL_MODE=false`, test failed
- without `SPARK_ANSI_SQL_MODE` env variable, test passed
2. Fixed with new test code
- with `SPARK_ANSI_SQL_MODE=false`, test passed
- without `SPARK_ANSI_SQL_MODE` env variable, test passed
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #47472 from wayneguow/fix_h2.
Authored-by: Wei Guo <[email protected]>
Signed-off-by: Hyukjin Kwon <[email protected]>
---
.../org/apache/spark/sql/jdbc/JDBCV2Suite.scala | 54 +++++++++++++---------
1 file changed, 32 insertions(+), 22 deletions(-)
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
index db06aac7f5e0..f4f73c98c04d 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
@@ -37,7 +37,7 @@ import org.apache.spark.sql.connector.expressions.Expression
import org.apache.spark.sql.execution.FormattedMode
import
org.apache.spark.sql.execution.datasources.v2.{DataSourceV2ScanRelation,
V1ScanWrapper}
import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog
-import org.apache.spark.sql.functions.{abs, acos, asin, avg, ceil, coalesce,
count, count_distinct, degrees, exp, floor, lit, log => logarithm, log10, not,
pow, radians, round, signum, sqrt, sum, udf, when}
+import org.apache.spark.sql.functions.{abs, acos, asin, atan, atan2, avg,
ceil, coalesce, cos, cosh, cot, count, count_distinct, degrees, exp, floor,
lit, log => logarithm, log10, not, pow, radians, round, signum, sin, sinh,
sqrt, sum, tan, tanh, udf, when}
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.test.SharedSparkSession
import org.apache.spark.sql.types.{DataType, IntegerType, StringType}
@@ -228,6 +228,19 @@ class JDBCV2Suite extends QueryTest with
SharedSparkSession with ExplainSuiteHel
stmt.setString(1, "jen")
stmt.setBytes(2, testBytes)
stmt.executeUpdate()
+
+ conn.prepareStatement("CREATE TABLE \"test\".\"employee_bonus\" " +
+ "(name TEXT(32), salary NUMERIC(20, 2), bonus DOUBLE, factor
DOUBLE)").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"employee_bonus\" " +
+ "VALUES ('amy', 10000, 1000, 0.1)").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"employee_bonus\" " +
+ "VALUES ('alex', 12000, 1200, 0.1)").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"employee_bonus\" " +
+ "VALUES ('cathy', 8000, 1200, 0.15)").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"employee_bonus\" " +
+ "VALUES ('david', 10000, 1300, 0.13)").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"employee_bonus\" " +
+ "VALUES ('jen', 12000, 2400, 0.2)").executeUpdate()
}
h2Dialect.registerFunction("my_avg", IntegralAverage)
h2Dialect.registerFunction("my_strlen", StrLen(CharLength))
@@ -1258,29 +1271,25 @@ class JDBCV2Suite extends QueryTest with
SharedSparkSession with ExplainSuiteHel
checkAnswer(df15, Seq(Row(1, "cathy", 9000, 1200, false),
Row(2, "alex", 12000, 1200, false), Row(6, "jen", 12000, 1200, true)))
- val df16 = sql(
- """
- |SELECT * FROM h2.test.employee
- |WHERE sin(bonus) < -0.08
- |AND sinh(bonus) > 200
- |AND cos(bonus) > 0.9
- |AND cosh(bonus) > 200
- |AND tan(bonus) < -0.08
- |AND tanh(bonus) = 1
- |AND cot(bonus) < -11
- |AND asin(bonus / salary) > 0.13
- |AND acos(bonus / salary) < 1.47
- |AND atan(bonus) > 1.4
- |AND atan2(bonus, bonus) > 0.7
- |""".stripMargin)
+ val df16 = spark.table("h2.test.employee_bonus")
+ .filter(sin($"bonus") < -0.08)
+ .filter(sinh($"bonus") > 200)
+ .filter(cos($"bonus") > 0.9)
+ .filter(cosh($"bonus") > 200)
+ .filter(tan($"bonus") < -0.08)
+ .filter(tanh($"bonus") === 1)
+ .filter(cot($"bonus") < -11)
+ .filter(asin($"factor") > 0.13)
+ .filter(acos($"factor") < 1.47)
+ .filter(atan($"bonus") > 1.4)
+ .filter(atan2($"bonus", $"bonus") > 0.7)
checkFiltersRemoved(df16)
checkPushedInfo(df16, "PushedFilters: [" +
- "BONUS IS NOT NULL, SALARY IS NOT NULL, SIN(BONUS) < -0.08, SINH(BONUS)
> 200.0, " +
+ "BONUS IS NOT NULL, FACTOR IS NOT NULL, SIN(BONUS) < -0.08, SINH(BONUS)
> 200.0, " +
"COS(BONUS) > 0.9, COSH(BONUS) > 200.0, TAN(BONUS) < -0.08, TANH(BONUS)
= 1.0, " +
- "COT(BONUS) < -11.0, ASIN(BONUS / CAST(SALARY AS double)) > 0.13, " +
- "ACOS(BONUS / CAST(SALARY AS double)) < 1.47, " +
- "ATAN(BONUS) > 1.4, (ATAN2(BONUS, BONUS)) > 0.7],")
- checkAnswer(df16, Seq(Row(1, "cathy", 9000, 1200, false)))
+ "COT(BONUS) < -11.0, ASIN(FACTOR) > 0.13, ACOS(FACTOR) < 1.47,
ATAN(BONUS) > 1.4, " +
+ "(ATAN2(BONUS, BONUS)) > 0.7],")
+ checkAnswer(df16, Seq(Row("cathy", 8000, 1200, 0.15)))
// H2 does not support log2, asinh, acosh, atanh, cbrt
val df17 = sql(
@@ -1759,7 +1768,8 @@ class JDBCV2Suite extends QueryTest with
SharedSparkSession with ExplainSuiteHel
Row("test", "empty_table", false), Row("test", "employee", false),
Row("test", "item", false), Row("test", "dept", false),
Row("test", "person", false), Row("test", "view1", false), Row("test",
"view2", false),
- Row("test", "datetime", false), Row("test", "binary1", false)))
+ Row("test", "datetime", false), Row("test", "binary1", false),
+ Row("test", "employee_bonus", false)))
}
test("SQL API: create table as select") {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
