[Bug]: Scale is ignored for decimal columns in prepared statements

[staticlibs]

What happened?

The following snippet returns 123.45 on Babelfish and 123.46 on MSSQL:

create table tab1(num decimal(5, 2))
declare @p1 int
set @p1 = null
exec sp_prepare @p1 output, N'@P0 decimal(38,3)', N'insert into tab1(num) values(@P0)', 1
exec sp_execute @p1, 123.456
select * from tab1

If we use numeric instead of decimal in either table column definition or in sp_prepare second argument (parameters definition) - then Babelfish correctly returns 123.46, both following examples work correctly on Babelfish:

create table tab1(num numeric(5, 2))
declare @p1 int
set @p1 = null
exec sp_prepare @p1 output, N'@P0 decimal(38,3)', N'insert into tab1(num) values(@P0)', 1
exec sp_execute @p1, 123.456
select * from tab1

create table tab1(num decimal(5, 2))
declare @p1 int
set @p1 = null
exec sp_prepare @p1 output, N'@P0 numeric(38,3)', N'insert into tab1(num) values(@P0)', 1
exec sp_execute @p1, 123.456
select * from tab1

The problem happens on insert, the scale value 2 included in column type modifier is ignored if column is declared as decimal. I can see that sys.decimal is defined as a DOMAIN and AFAIU Postgres does not support type modifiers for domains.

The problem with sp_prepare + sp_execute happens the same way for both T-SQL and TDS RPC invocations.

The problem also happens with sp_executesql when it is called in T-SQL:

create table tab1(num decimal(5, 2))
exec sp_executesql N'insert into tab1(num) values(@P0) ', N'@P0 decimal(38,3)', 123.456
select * from tab1

But it does NOT happen when sp_executesql is called with TDS RPC. This code path is used with prepared statements in mssql-jdbc driver. Because, unlike sp_prepare + sp_execute calls, with sp_executesql actual parameter values are passed in the same call, their type ID is set to numeric instead of decimal and this type ID is preserved in pltsql_declare_variable() and then is made available to executor from exec_eval_datum().

As a workaround, it is possible to rewrite decimal to numeric in read_param_def() before passing type names to type IDs lookup. This fixes the problem for jTDS driver (that uses sp_prepare + sp_execute RPC for prepared statements). But I assume the problem requires more general solution so do not propose it as a fix.

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

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

Fedora

Relevant log output

No response

Code of Conduct

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