[Bug]: BCP cannot import table with time(7) column

[staticlibs]

What happened?

Using the latest bcp utility:

bcp -v

BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 16.0.1000.6

And the table in MSSQL like this:

create table tab1(
    col1 time(7)
)

insert into tab1 values ('00:00:00.1234567')

This table is exported from MSSQL using the native binary format:

bcp tab1 out tab1.bcp -n -S 127.0.0.1,1433 -U sa -P pwd

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (1000.00 rows per sec.)

The same table is created in Babelfish, the type modifier for col1 is saved in a catalog as "unspecified":

select atttypmod from pg_attribute where attname = 'col1'

atttypmod
---------------
-1

And sp_describe_first_result_set returns type modifier 6 for this column:

exec sp_describe_first_result_set 'select * from tab1'

scale
-------
6

When we try to import into Babelfish that tab1.bcp file exported from MSSQL, the following error occurs:

bcp tab1 in tab1.bcp -n -S 192.168.178.58,1433 -U jdbc_user -P 12345678

Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 47

The bcp utility uses sp_describe_first_result_set to get column types for the specified table. Based on its return it generates the following bulk copy query:

insert bulk tab1([col1] time(6))

And the error happens on the client side in ODBC driver when it tries to bind the time(7) value from the input file to the time(6) parameter.

It is unclear how to approach this problem on server side (perhaps the unused 7 type modifier can be retained in attoptions catalog field?). And AFAIU there are no workarounds to this on client side (with bcp) for binary files exported from MSSQL which contain time(7) or datetime2(7) columns.

Version

BABEL_4_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct.

[runlevel5]

I too experience the same issue in which the datetime2(7) in the source DB get rounded to datetime2(6) in the target DB.

[staticlibs]

To clarify, the problem here is not that Babelfish accepts datetime2(7) column definition and then processes it as datetime(6). This is reasonable approach to use Postgres native timestamp data type with max 6 fractional digits for seconds.

The problem is that when the DDL introspection is used on the column created as datetime2(7) , its definition is returned as datetime2(6). This breaks data migration scenarios that rely on bcp (that uses this introspection under the hood).

There is a workaround that seems to be not too disruptive - to return the type modifier 7 instead of 6 in introspection of columns that do not have the exact value set for this modifier (-1 is currently set for 7 input):

diff --git a/contrib/babelfishpg_tsql/sql/sys_views.sql b/contrib/babelfishpg_tsql/sql/sys_views.sql
index 796ecd34..f917200b 100644
--- a/contrib/babelfishpg_tsql/sql/sys_views.sql
+++ b/contrib/babelfishpg_tsql/sql/sys_views.sql
@@ -149,13 +149,13 @@ BEGIN
                WHEN 'date' THEN scale = 0;
                WHEN 'datetime' THEN scale = 3;
                WHEN 'smalldatetime' THEN scale = 0;
-               WHEN 'datetime2' THEN scale = 6;
-               WHEN 'datetimeoffset' THEN scale = 6;
+               WHEN 'datetime2' THEN scale = 7;
+               WHEN 'datetimeoffset' THEN scale = 7;
                WHEN 'decimal' THEN scale = 38;
                WHEN 'numeric' THEN scale = 38;
                WHEN 'money' THEN scale = 4;
                WHEN 'smallmoney' THEN scale = 4;
-               WHEN 'time' THEN scale = 6;
+               WHEN 'time' THEN scale = 7;
                WHEN 'tinyint' THEN scale = 0;
                ELSE
                        IF return_null_for_rest

This can be applied manually to existing DB cluster by recreating sys.tsql_type_scale_helper function replacing 6 return values with 7.