babelfish-for-postgresql / babelfish_extensions / 17271276076 / 1
77%
BABEL_5_X_DEV: 77%

DEFAULT BRANCH: BABEL_5_X_DEV
Ran
Files 120
Run time 7s
Badge
Embed ▾
README BADGES
x

If you need to use a raster PNG badge, change the '.svg' to '.png' in the link

Markdown

Textile

RDoc

HTML

Rst
coverage: 76.103% (+0.002%) from 76.101%
17271276076.1

push

github

web-flow 
Fix datetime roundoff mechanism during storing/insertion of data (#4018)

Datetime datatype in TSQL has a precision of 3 ms and this behaviour is persisted in all operations i.e., qual conditions, operators, cast etc. However, babelfish only follows this rule during returning rows. In all other operations, it does NOT apply any round off mechanism (relies on PG’s handling).

It is a day one issue in babelfish where we internally store the original value for datetime datatype and not the one rounded to increments of .000, .003, or .007 seconds.
For Example in this case:

CREATE TABLE dbo.DateTimeTest (
    id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    lastmodifieddate DATETIME NULL
);
INSERT INTO dbo.DateTimeTest (lastmodifieddate)
VALUES (CAST('2021-09-20T04:00:49.299' AS DATETIME2(3)));

The value stored in lastmodifieddate column in babelfish would actually be '2021-09-20T04:00:49.299' and not '2021-09-20T04:00:49.300'

So, when we select the datetime column:

SELECT * FROM dbo.DateTimeTest;

Then the TDS Sender function for datetime kick in and rounds off the value to '2021-09-20T04:00:49.300'

For other cases, the original value is used for any operations performed!
Example in the second case:

SELECT * FROM dbo.DateTimeTest WHERE lastmodifieddate = '2021-09-20T04:00:49.300';

The = operator would compare with original value of lastmodifieddate column i.e. '2021-09-20T04:00:49.299' and hence no rows are returned.

To solve this issue, rather than rounding off the values while sending the rows to user, we should store the values in rounded off format.

Tasks: BABEL-5265, BABEL-1081

Authored-by: Shameem Ahmed shmeeh@amazon.com

50072 of 65795 relevant lines covered (76.1%)

376435.91 hits per line

Source Files on job 17271276076.1