issue due to rounding of timestamps

[AlBundy33]

Driver version

10.2.0

SQL Server version

Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
Jan 12 2022 22:30:08
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS)

Client Operating System

Windows 10 but also affects Ubuntu

JAVA/JVM version

OpenJDK 11 but also affects Oracle Java 8

Description

We've recently tried to switch from jTDS to mssql-jdbc-driver which seems to work pretty good in most cases.
But there seems to be an issue due to the rounding of timestamps in mssql-jdbc because if we write a tiemstamp with System.currentTimeMillis() in the database, read the value from the database and try to update all records with timestamp >= the previous read tiemstamp this doesn't work for some values.

Test-case

I hope this test-case will describe the problem better :-)
-> This test-case is similar to some logic in our application that currently fails in our integration tests.

I've used com.microsoft.sqlserver.jdbc.preparedStatement.SetObjectTest as base - not sure if this is the right place.

    @Test
    public void testTimestampRounding()
        throws SQLException
    {
      try (Connection con = getConnection())
      {
        final String tableName = "sometesttable";
        try (Statement stmt = con.createStatement())
        {
          stmt.executeUpdate("CREATE TABLE " + AbstractSQLGenerator.escapeIdentifier(tableName)
              + " (id INT PRIMARY KEY, ts datetime)");

          try
          {
            final long millis = System.currentTimeMillis();
            for (int i = 0; i < 100; i++)
            {
              // 1. create a timestamp
              final Timestamp preparedTimestamp = new Timestamp(millis + i);
              // 2. insert timestamp
              try (PreparedStatement pstmt = con.prepareStatement(
                  "INSERT INTO " + AbstractSQLGenerator.escapeIdentifier(tableName) + " (id, ts) VALUES (?, ?)"))
              {
                pstmt.setInt(1, i);
                pstmt.setObject(2, preparedTimestamp);
                pstmt.executeUpdate();
              }

              // 3. read timestamp from db
              Timestamp timestampFromDatabase = null;
              try (PreparedStatement select = con.prepareStatement("select ts from " + AbstractSQLGenerator.escapeIdentifier(tableName)
                + "where id = ?"))
              {
                select.setObject(1, i);
                try (ResultSet rs = select.executeQuery())
                {
                  Assert.assertTrue(rs.next());
                  timestampFromDatabase = rs.getTimestamp(1);
                }
              }
              Assert.assertNotNull(timestampFromDatabase);

              // 4. now try to update all records for our ID (I know - it's the PK ...) and where the ts >= as the timestamp read from database
              // -> the expceted behavior is, that the record is updated but this doesn't work allways - I think this is due to some roundings in the driver
              try (PreparedStatement update = con.prepareStatement("update " + AbstractSQLGenerator.escapeIdentifier(tableName) + " set ts = null where id = ? and ts >= ?"))
              {
                update.setObject(1, i);
                update.setObject(2, timestampFromDatabase);
                assertEquals(1, update.executeUpdate(),
                    "i=" + i + ", timestampFromDatabase=" + timestampFromDatabase + ", millies=" + timestampFromDatabase.getTime() + ", nanos=" + timestampFromDatabase.getNanos()
                    + ", preparedTimestamp=" + preparedTimestamp);
              }
            }
          }
          finally
          {
            TestUtils.dropTableIfExists(AbstractSQLGenerator.escapeIdentifier(tableName), stmt);
          }
        }
      }
    }

I don't know if step 3 or 4 is the problem but this test always fails with my local database with very low values of i.

The expected behavior would be that I could search with the original values from database.

[VeryVerySpicy]

Hi @AlBundy33, thanks for reaching out, we will take a look at this issue soon.

[VeryVerySpicy]

Hi @AlBundy33,

Could you elaborate on what your expected behavior is? The program appears to be "working" properly when I run it. The assert will fail whenever it encounters a row where it doesn't need to update anything.

[AlBundy33]

If you get a timestamp (ts) from db and then query or update all records where the timestamp-column >= ts i would expect that also the row is updated where i got the timestamp from.

In my first example the row with the timestamp was not updated even the timestamp >= ts because i got it first from database.

Another example (not tested but makes it hopefully clear)
ts = select min(timestamp_column) from sometable

This should now get all records including the row where we get the timestamp from in first place
select * from sometable where timestamp_column >= ts

but depending on the used millis it seems that the original record is skipped.

If you execute the posted test it should fail.

[VeryVerySpicy]

Comparing a java Timestamp to a datetime column on sql server will not have accurate results.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16
Datetime actually rounds to the .000, .003, or .007 of seconds.

[AlBundy33]

i know - but if i get the timestamp from the database on the first hand i would expect that is a usabe timestamp.
so insert a java timestamp may result in a rounded value in the database - this is managed by the db.
but with msql-jdbc it seems that this value is rounded again in some way if i get the timestamp from the db on the first hand.
and in my opinion this is a bug in the driver.

if i do a select max(ts_col) from sometable where ... to find a timestamp where i have to start some processing from i would expect that the row(s) with this timestamp are part of the resultset select * from sometable where ts_col >= ? (where ? = the result of the max-query)

so it seems that the driver rounds timestamps that are already coming from the database.

[David-Engel]

This is an issue with how the database handles comparisons between datetime and datetime2 values. The driver sends timestamp parameters as datetime2, but the column is a datetime. (This worked great up until SQL Server 2016.) When the database converts the datetime to a datetime2 for comparison with the more precise datetime2 parameter, you can get unexpected results.

For more details and workarounds, see these links:
https://github.com/Microsoft/mssql-jdbc/wiki/Addressing-datetime-to-datetime2-conversion-behavior-change-starting-from-SQL-Server-2016

#680
(Particularly this comment: #680 (comment))

Due to the data handling accuracy "improvements" in 2016, datetime values are made more "accurate" when converting to datetime2 since datetime is actually granular to 1/300 of a second.

Regards,
David

[AlBundy33]

So you don't think that this behavior is a bug?

If I read a valie A from database and query all records with value A afterwards I would expect to get all the records including that one where I've got value A from in first place.

With jTDS driver for example this issue doesn't exists.

[VeryVerySpicy]

It may be unintuitive but yes this is the expected behavior. As you can see from the example TSQL in issue 680, even comparing the exact "same" datetime2 to datetime value within SSMS will result in an inequality.

When you read value A the server will give the driver a time value which for example ends in .003 seconds. When you query the database against this same column we then pass back that value which ends in .003 seconds, however when the server makes the comparison it will compare the value we passed with the stored value of .00<1 third of a millisecond>. Which will result in an inequality since 0.003 != 0.0033333333...

I suspect that jTDS still sends data as datetime instead of datetime2. However it would not make sense for us to do that since datetime2 is the more accurate datatype.

If you run the repro app with the column type as datetime2(3) the comparison will work for all rows.

[VeryVerySpicy]

Hello @AlBundy33,
Does that answer your queries regarding this issue?

[AlBundy33]

I'm not really sure about that. 😄
I totally understand the problem mapping a timestamp to datetime and datetime2.
And I would also choose the datatype with the higher precission.

The problem is that the current behavior is totally unxpected when working with datetime.
and because there is no exception and "just" missing or to much rows that may result in unexpected application states.

Setting compatibility mode to an older version may workaround the problem but if we forget to change the settings in existing and future customer database we have rhe same problem as mentioned above.
Also I'm not sure how long a specific compat-mode is supported by sql server.

Is there really no chance that you can handle this on driver side?
What about a "low-precission-datetime-mode" that sends timestamps always as datetime?
I would expect that most applications using just one of the datatypes.

[VeryVerySpicy]

There probably are ways to handle it driver side, and you are free to make a feature request, however this is simultaneously

1. Probably not trivial to implement
2. Would have to have server version specific behavior
3. Not high priority since datetime2 does exist and works

So it would take a long time and likely a rather unexpectedly smooth semester to get this in.

[AlBundy33]

It seems that #443 describes the same issue and already has a linked feature-request #1590 with a pull-request.

It would be really good to have an option to choose between how timestamps are interpreted.

[AlBundy33]

Problem is also described in #1520

[Jeffery-Wasty]

#1687 resolves this by allowing users to specify the datatype to use for date/timestamp parameters. @AlBundy33, please let us know if this fix (included in 12.2+) works for you. Closing issue, but can reopen if needed.

[AlBundy33]

thank you - seems to work 👍