Closed ecki closed 1 year ago
This seems to be the different castings the two drivers use (from profiler of other timestamp):
MS JDBC driver:
exec sp_executesql N'SELECT * FROM t2 WHERE col=@P0',N'@P0 datetime2','2021-02-15 14:59:53.2830000'
JTDS JDBC driver;
declare @p1 int
set @p1=2
exec sp_prepare @p1 output,N'@P0 datetime',N'SELECT * FROM t2 WHERE col= @P0 ',1
select @p1
exec sp_execute 2,'2021-02-15 14:59:53.283'
Somewhat related to https://github.com/microsoft/mssql-jdbc/issues/680
hi @ecki
Thank you for the suggestion, the team will review this.
Thank you for the suggestion, the team will review this.
Thanks for response. I understand from #680 that it might be hard to cater for the changed server behavior, but it looks like if you would "just" cast the literal to the actual type (datetime not datetime2) that it would solve the problems.
I have updated the reproducer (program and output) to make the problem a bit clearer. You can see that jtds driver matches all 4 query conditions on the datetime column and that mssql-jdbc matches only 2 (for older compat level) and none for recent compat levels. (This now looks more like a bug, not only a feature request!)
Does it make sense to propose a patch for this? If so, any specific issues to look out for?
Problem: rounded DATETIME timestamps do not match
When using the old DATETIME data type in SQL Server some precision of timestamps is lost. This is fixed with DATETIME2, however there are situations where an installation needs to keep the old data type DATETIME.
With the jTDS driver this is not a big problem, the following code will insert a timestamp and match the same timestamp:
given: CREATE TABLE T1(id int, d1 DATETIME, d2 DATETIME2)
p = c.prepareStatement("INSERT T1(1,?,?);"); p.setTimestamp(1, new Timestamp(12345)); p.setTimestamp(2, new Timestamp(12345)); p.execute();
p = c.prepareStatement("SELECT * from T1 where D1=?"); p.setTimestamp(1, new Timestamp(12345));
sqlcmd (after code run with jtds driver):
as you can see, the rounding is done on insert not only for the d1 column but also in the more precise d2.
The same insert with the MSSQL driver looks better as it uses the full datetime2 portential:
The only problem is that the select does not match this row on d1 when given the same timestamp as was inserted,
(whats worse the new Timestamp(123457) (rounded) value (as read back from the DB) also does not compare with later compat levels, see code below)
I have seen there is some casting (DATETIME2?) going on in the raw sent prepared statements, maybe this is the reason, or maybe just that jTDS does the rounding client side or using another data type to send?
Preferred Solution: round/cast datetime bind parameters
It would be good that for legacy applications (datetime) a mode can be enabled which makes the INSERT/SELECT cycle work with mssql-jdbc regardless of the SQL Server compat level. on datetime columns.
Not exactly sure how this is technically done (casting or rounding) and if it can be done with low impact depending on the column type. If it cant know the column type and dynamically figure it out at least a connection parameter to switch to sending timestamps compatible with datetime 2 would be good.
Describe alternatives you've considered
Alternatives are staying with jTDS (which has other problems), rounding in the application (ugly in codebase) or changing the data type. The last would be possible (and best solution in the long run), but it is a larger project to make that for the large installation base.
Sampe Output (jTDS)
(timezone is UTC+01:00)
Sample Output mssql-jdbc
Sample Reproducer
(works with url1 (jtds) to list a match in both cases but not with url2 (mssql))