[CAY-2058] Treating TIME jdbc-type as datetime db-type for MS SQL Server

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.M2, 4.0.M3
• Fix Version/s: 4.0.M5
• Assignee: Nikita Timofeev
• Reporter: Savva Kolbachev
• Created: 10/Feb/16 09:02
• Updated: 21/Jan/17 13:13
• Resolved: 21/Jan/17 13:10

Description

Relates to the failed test DataContextEJBQLDateTimeFunctionalExpressionsIT.testCURRENT_TIME:85

We treats TIME jdbc-type as datetime db-type for MS SQL Server:

   <jdbc-type name="TIME">
       <db-type name="datetime"/>
   </jdbc-type>

For testCURRENT_TIME we have the following insertion query:

INSERT INTO DATE_TEST (DATE_COLUMN, DATE_TEST_ID, TIMESTAMP_COLUMN, TIME_COLUMN) VALUES (?, ?, ?, ?)
[batch bind: 1->DATE_COLUMN:NULL, 2->DATE_TEST_ID:220, 3->TIMESTAMP_COLUMN:NULL, 4->TIME_COLUMN:'2016-02-01 23:59:59.753']
[batch bind: 1->DATE_COLUMN:NULL, 2->DATE_TEST_ID:221, 3->TIMESTAMP_COLUMN:NULL, 4->TIME_COLUMN:'2016-02-01 00:00:00.753']

But actually in the database we have:

TIME_COLUMN (datetime)
1970-01-01 00:00:00.383
1970-01-01 23:59:59.383

Then testCURRENT_TIME compare time column with current time and fails.

SELECT t0.DATE_COLUMN AS ec0_0, t0.TIME_COLUMN AS ec0_1, t0.TIMESTAMP_COLUMN AS ec0_2, t0.DATE_TEST_ID AS ec0_3 FROM DATE_TEST t0 WHERE t0.TIME_COLUMN < {fn CURTIME()}

Treating TIME jdbc-type as time db-type solves this problem, but it also breaks backward compatibility with 2005 version. MS SQL Server has supported time db-type since 2008 version.