NOTICE: DB-Library notice: Msg #: 243, Msg state: 1, Msg: Type numericame is not a defined system type

[maxstarkov]

Issue report

Sometimes the execution of query is interrupted with an error:

NOTICE: DB-Library notice: Msg #: 243, Msg state: 1, Msg: Type numericame is not a defined system type., Server: f517f202eb9c, Process: , Line: 1, Level: 16

It seems that tds_fdw incorrectly prepares the query text, where numeric types are used in the selection conditions.

Operating system

Distributor ID: Ubuntu
Description:    Ubuntu 20.04.5 LTS
Release:        20.04
Codename:       focal

Version of tds_fdw

tds_fdw | 2.0.3   | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

Version of PostgreSQL

PostgreSQL 14.5 (Ubuntu 14.5-2.pgdg20.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

Version of FreeTDS

freetds-common             1.1.6-1.1

Sentences, data structures, data

Reproducing the bug.
Create a table on the SQL Server:

1> create database test;
2> go
1> use test;
2> go
Changed database context to 'test'.
1> create table t1 (f1 numeric(9,0));
2> go
1> insert into t1 values (100), (101);
2> go

(2 rows affected)
1> select * from t1;
2> go
f1         
-----------
        100
        101

On PostgreSQL, create a foreign table to the table created on the SQL Server and execute the query several times:

postgres=# select * from mssql.t1 where f1 > 100.99;
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'test'., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'test'., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: f517f202eb9c, Process: , Line: 1, Level: 0
 f1 
----
(0 rows)

After several executions, the query will fail with an error:

postgres=# select * from mssql.t1 where f1 > 100.99;
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'test'., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 102, Msg state: 1, Msg: Incorrect syntax near 'dbo'., Server: f517f202eb9c, Process: , Line: 1, Level: 15
ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
postgres=# select * from mssql.t1 where f1 > 100.99;
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'test'., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: f517f202eb9c, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 243, Msg state: 1, Msg: Type numericM is not a defined system type., Server: f517f202eb9c, Process: , Line: 1, Level: 16
ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

I found and fixed this error.
The error occurs when tds_fdw handles comparable data types and does it wrong:

size_t len = strlen(postgresql_type);

tds_type = palloc(len);
strncpy(tds_type, postgresql_type, len);

strncpy does not terminate the buffer tds_type with a null character, so not only the data type name is substituted into the query text, but any other bytes from the buffer.
To fix the error, I changed this code to the following:

tds_type = (char *) palloc((len + 1) * sizeof(char));
sprintf(tds_type, "%s", postgresql_type);

I can prepare a pull request if needed.