Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

BigQuery: Timestamp rounding issue #1644

Closed
liu-du opened this issue Oct 8, 2021 · 0 comments
Closed

BigQuery: Timestamp rounding issue #1644

liu-du opened this issue Oct 8, 2021 · 0 comments
Assignees
Labels
api: bigquery Issues related to the googleapis/java-bigquery API.

Comments

@liu-du
Copy link

liu-du commented Oct 8, 2021

Environment details

  1. BigQuery
  2. OS type and version: macOS
  3. Java version: 8
  4. bigquery version(s): at commit: 2b942da

Code example

I run the following query (note the last 3 digits are 574):

SELECT TIMESTAMP '2022-01-24T23:54:25.095574Z'

but I get back (note the last 3 digits are 573):

2022-01-24T23:54:25.095573Z

The following is a screen shot of the test and the result that I added to src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java:

BigQueryTimestamp

Seems the java client is affected by this issue, If I run the same query in BigQuery web console, the query returns correctly:
Screen Shot 2021-10-08 at 6 39 28 pm

Steps to reproduce

apply this patch and run the "tmpTest" in src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java.

Any additional information below

  • Only certain timestamp values may cause the rounding error
  • it seems that query priority has to be set to INTERATIVE to reproduce the rounding error
  • Although BigQuery Timestamp is up to microsecond according to documentation, but the value in FieldValue (see screenshot above) actually returns 7 digits instead of 6 (again this only happens for certain timestamps).

the following patch fixes the problem by rounding the big decimal to neareast whole number before taking the longValue:

diff --git a/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/FieldValue.java b/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/FieldValue.java
index a2ce187..5bab4b3 100644
--- a/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/FieldValue.java
+++ b/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/FieldValue.java
@@ -25,6 +25,7 @@ import com.google.common.base.MoreObjects;
 import com.google.common.io.BaseEncoding;
 import java.io.Serializable;
 import java.math.BigDecimal;
+import java.math.RoundingMode;
 import java.util.List;
 import java.util.Map;
 import java.util.Objects;
@@ -183,7 +184,7 @@ public class FieldValue implements Serializable {
     // timestamps are encoded in the format 1408452095.22 where the integer part is seconds since
     // epoch (e.g. 1408452095.22 == 2014-08-19 07:41:35.220 -05:00)
     BigDecimal secondsWithMicro = new BigDecimal(getStringValue());
-    BigDecimal scaled = secondsWithMicro.scaleByPowerOfTen(6);
+    BigDecimal scaled = secondsWithMicro.scaleByPowerOfTen(6).setScale(0, RoundingMode.HALF_UP);
     return scaled.longValue();
   }
@product-auto-label product-auto-label bot added the api: bigquery Issues related to the googleapis/java-bigquery API. label Oct 8, 2021
@stephaniewang526 stephaniewang526 self-assigned this Oct 8, 2021
stephaniewang526 added a commit to stephaniewang526/java-bigquery that referenced this issue Oct 8, 2021
stephaniewang526 added a commit to stephaniewang526/java-bigquery that referenced this issue Oct 8, 2021
Fixes googleapis#1644

remove unrelated test case
stephaniewang526 added a commit to stephaniewang526/java-bigquery that referenced this issue Oct 8, 2021
Fixes googleapis#1644

remove unrelated test case

update test case
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: bigquery Issues related to the googleapis/java-bigquery API.
Projects
None yet
Development

No branches or pull requests

2 participants