babelfish-for-postgresql / babelfish_extensions / 17260507915

76%

BABEL_5_X_DEV: 77%

Build Type

push

github

Committed by web-flow

Commit Message

Fix datetime roundoff mechanism during storing/insertion of data (#4045)

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

Signed-of-by: Shameem Ahmed shmeeh@amazon.com

Run Details

50 of 54 new or added lines in 7 files covered. (92.59%)

4 existing lines in 2 files now uncovered.

49496 of 65492 relevant lines covered (75.58%)

327366.0 hits per line