SNOW-1196082: Inserting and reading timestamps is not symetric if too much columns inserted with batch

[ericcournarie]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?
   3.15.0

2. What operating system and processor architecture are you using?
   Mac or Alma Linux

3. What version of Java are you using?
   Java 8

4. What did you do?

After inserting rows with timestamps values using PreparedStatement and batch, when reading back the rows , some values are shifted. The shift actually depends of the timezone of the machine you are running on ( be sure not to be on UTC to see the problem)
Note that this does not happen with older driver version like 3.13.15

It depends on 'how many columns' are inserted. With 3, there was no problem, with 8, the problem appears

The following program describe the problem (just change the boolean bug to false or true to exercise with 3 or 8 columns )

report_bug_timestamp.txt

5. What did you expect to see?

   The values read should be the one that was written

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

7. What is your Snowflake account identifier, if any? (Optional)

[sfc-gh-sghosh]

Hello @ericcournarie ,

Thank you for raising the issue and sample application, we are taking a look.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @ericcournarie ,

I tried the sample java application with the latest Snowflake JDBC driver 3.15.0, its giving the expected output, not throwing error.

The timezone is set to stmt.execute("alter session set timezone = 'America/New_York'");

Here is the application
if inserting ps.setTimestamp(2, new Timestamp(1403049600000L), utc);
Getting 1403049600000

if inserting ps.setTimestamp(2, new Timestamp(1388016000000L), utc);
Getting 1388016000000

sample_con_Timestamp.txt

Regards,
Sujan

[ericcournarie]

Hi Sujan,
thanks for testing but
you should not reduce the number of lines so much, or the bug will not appear

[sfc-gh-sghosh]

Hello @ericcournarie ,

I tried even the full sample jdbc application as it is but still its not throwing any incorrect output.

I tried with latest jdbc 3.15.0 and set timezone for the user as timezone='UTC' etc.

Please try the following code, could you please try from a different client machine and use latest snowflake jdbc driver, please capture jdbc log as well.

sample_app.txt

Regards,
Sujan

[ericcournarie]

Hi Sujan,

it works with yours, but this is not the one I gave you

please test with if (row % 10000 == 0) { instead of your if (row % 1000 == 0) {

in my case it fails while it was working with yours

the bug should appear . it looks like it is related to a 'sizing' effect, as with less columns and less lines, the bug does not appear

thanks

[sfc-gh-sghosh]

Hello @ericcournarie ,

Thanks for the update.
I used same code if (row % 10000 == 0) and checked with by setting various timezone ( and confirmed they set properly ) , but still the issue not getting reproduced, no error being thrown, no incorrect output.

stmt.execute("alter session set timezone = 'America/New_York'"); // CurrentTimestamp -> 1967-06-23 03:00:00.123 -0400
stmt.execute("alter session set timezone = 'UTC'"); // CurrentTimestamp -> 1967-06-23 07:00:00.123 Z
stmt.execute("alter session set timezone = 'Australia/Sydney'"); // CurrentTimestamp -> 1967-06-23 17:00:00.123 +1000

Here is the updated jdbc application, you can try running from a different client machine and capture the jdbc trace if able to reproduce.

jdbc_app.txt

Regards,
Sujan

[ericcournarie]

Hello Sujan,

Sorry, but once again, your program is not correct and not the one you should run

you have for (int row = 0; row < 30; row++) { when the one I provided to you is for (int row = 0; row < 30000; row++) {

by just changing this, I ran into the problem with your app.

Thanks

[sfc-gh-sghosh]

Hello Eric,

With loop (int row = 0; row < 30000; row++), its not throwing error but just a warning, but thats is not related to this issue.
WARNING: JAXB is unavailable. Will fallback to SDK implementation which may be less performant.If you are using Java 9+, you will need to include javax.xml.bind:jaxb-api as a dependency.

There is no other error being thrown, could you please try from a different client machine and capture jdbc logs.

Regards,
Sujan

[ericcournarie]

Hello Sujan,

I have tried on several machine (Unix and Mac) with several Java version (8,11,17). As said, the Java machine should not be on UTC to see the bug.
This is different from the timezone set on Snowflake session

Can you add the following at the start of your program to test, so to be sure

       System.out.println(" Timezone :"  + TimeZone.getDefault().toString());
       // change default Java  timezone
        TimeZone.setDefault(TimeZone.getTimeZone("Australia/Sydney"));
        System.out.println(" Timezone :"  + TimeZone.getDefault().toString());

Thanks

For some reason, cannot get the log . I have added 'tracing=ALL' , but nothing is outputted..

[sfc-gh-sghosh]

Hello Eric,

Thanks for the update.
When I put the TimeZone.setDefault(TimeZone.getTimeZone("Australia/Sydney")); st starting after connection and statement, I am able to reproduce it. We are working on it.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @ericcournarie ,

The engineering team updated that "the timezone setting for the snowflake server is not the same as the timezone on the application" This is the root cause of the issue.

This issue did not happen on version 3.13.15 because the driver had uploaded the timestamp with only the UTC zone before version 3.13.22. A new feature has been applied since version 3.13.22 was released on bdb8546, and you can see that the timestamp value is converted with the timezone based on the system time zone( the customer machine).

However, when timestamp data is uploaded with the regular insert query, this data does not affect the timezone of the client machine, but the timezone of the snowflake server. This is to say that if the application does not change the timezone setting of the server with "alter session set timezone", the timestamp value will not be converted.

Therefore, if you do not set the timezone the same as the application timezone, the first row of the data will have a different timezone value on the sample application code. To resolve this issue, could you please execute the alter session set timezone = "the timezone you want " before inserting the data.

Regards,
Sujan

[ericcournarie]

Hello Sujan,

Sadly, setting the timezone does not change the behaviour, the data when reading back is still not the same as the one written.
This workaround does not seem to work.
Even with a alter session set timezone = 'UTC'

Regards,
Eric

[sfc-gh-sghosh]

Hello @ericcournarie ,

The engineering team working on the fix, meanwhile could you let us know the value of the session parameter 'CLIENT_TIMESTAMP_TYPE_MAPPING' .

You can get it this way
`rstime= stmt.executeQuery("SHOW PARAMETERS LIKE 'CLIENT_TIMESTAMP_TYPE_MAPPING'");

 // Processing the result set
 while (rstime.next()) {
     String parameterName = rstime.getString("key");
     String parameterValue = rstime.getString("value");
     System.out.println("Parameter Name: " + parameterName);
     System.out.println("Parameter Value: " + parameterValue);
 }`

Regards,
Sujan

[adescamps]

Hello @sfc-gh-sghosh

@ericcournarie being in vacation, I will respond.

The command returns:

Parameter Name: CLIENT_TIMESTAMP_TYPE_MAPPING
Parameter Value: TIMESTAMP_LTZ

If needed few others params that you may be interested in:

Parameter Name: TIMEZONE
Parameter Value: Europe/Paris

Parameter Name: TIMESTAMP_TYPE_MAPPING
Parameter Value: TIMESTAMP_NTZ

Parameter Name: JDBC_USE_SESSION_TIMEZONE
Parameter Value: true

Parameter Name: JDBC_TREAT_TIMESTAMP_NTZ_AS_UTC
Parameter Value: false

Regards,
Aurelien

[sfc-gh-sghosh]

Hello @ericcournarie ,

The fix has been delivered in Snowflake JDBC 3.17.0, I checked as well, its working fine now, no error.
Please check.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @ericcournarie ,

Closing this ticket as the issue has been fixed and resolved.

Regards,
Sujan

[ericcournarie]

Hello Sujan,

sorry I was out this summer . I confirm it's ok, thanks