This is an automated email from the ASF dual-hosted git repository.
yangjie01 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 69afd4be9c93 [SPARK-47361][SQL] Derby: Calculate suitable precision
and scale for DECIMAL type
69afd4be9c93 is described below
commit 69afd4be9c93cb31a840b969ed1984c0b6b92f8e
Author: Kent Yao <y...@apache.org>
AuthorDate: Thu May 30 17:31:28 2024 +0800
[SPARK-47361][SQL] Derby: Calculate suitable precision and scale for
DECIMAL type
### What changes were proposed in this pull request?
When storing `decimal(p, s)` to derby, if `p > 31`, `s` is wrongly
hardcoded to `5` which is the assumed default scale of derby decimal. Actually,
0 is the default scale, 5 is the default precision
https://db.apache.org/derby/docs/10.13/ref/rrefsqlj15260.html
This PR calculates a suitable scale to make room for precision.
### Why are the changes needed?
avoid precision loss
### Does this PR introduce _any_ user-facing change?
Yes, but derby is rare in production environments, and the new mapping are
compatible for most usecases
### How was this patch tested?
new tests
### Was this patch authored or co-authored using generative AI tooling?
no
Closes #46776 from yaooqinn/SPARK-48439.
Authored-by: Kent Yao <y...@apache.org>
Signed-off-by: yangjie01 <yangji...@baidu.com>
---
.../main/scala/org/apache/spark/sql/jdbc/DerbyDialect.scala | 12 +++++++++---
.../datasources/v2/jdbc/DerbyTableCatalogSuite.scala | 8 ++++++++
2 files changed, 17 insertions(+), 3 deletions(-)
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/DerbyDialect.scala
b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/DerbyDialect.scala
index 36af0e6aeaf1..23da4dbb60a5 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/DerbyDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/DerbyDialect.scala
@@ -48,9 +48,15 @@ private case class DerbyDialect() extends JdbcDialect {
case ByteType => Option(JdbcType("SMALLINT", java.sql.Types.SMALLINT))
case ShortType => Option(JdbcType("SMALLINT", java.sql.Types.SMALLINT))
case BooleanType => Option(JdbcType("BOOLEAN", java.sql.Types.BOOLEAN))
- // 31 is the maximum precision and 5 is the default scale for a Derby
DECIMAL
- case t: DecimalType if t.precision > 31 =>
- Option(JdbcType("DECIMAL(31,5)", java.sql.Types.DECIMAL))
+ // 31 is the maximum precision
+ // https://db.apache.org/derby/docs/10.13/ref/rrefsqlj15260.html
+ case t: DecimalType =>
+ val (p, s) = if (t.precision > 31) {
+ (31, math.max(t.scale - (t.precision - 31), 0))
+ } else {
+ (t.precision, t.scale)
+ }
+ Option(JdbcType(s"DECIMAL($p,$s)", java.sql.Types.DECIMAL))
case _ => None
}
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/DerbyTableCatalogSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/DerbyTableCatalogSuite.scala
index e3714e604495..d793ef526c47 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/DerbyTableCatalogSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/DerbyTableCatalogSuite.scala
@@ -51,4 +51,12 @@ class DerbyTableCatalogSuite extends QueryTest with
SharedSparkSession {
checkAnswer(sql(s"SHOW TABLES IN derby.test1"), Row("test1", "TABLE2",
false))
}
}
+
+ test("SPARK-48439: Calculate suitable precision and scale for DECIMAL type")
{
+ withTable("derby.test1.table1") {
+ sql("CREATE TABLE derby.test1.table1 (c1 decimal(38, 18))")
+ sql("INSERT INTO derby.test1.table1 VALUES (1.123456789123456789)")
+ checkAnswer(sql("SELECT * FROM derby.test1.table1"), Row(1.12345678912))
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org
For additional commands, e-mail: commits-h...@spark.apache.org
