[Bug]: sp_columns_100: Returned type character varying does not match expected type "varchar"

[staticlibs]

What happened?

I am running BABEL_2_2_1__PG_14_5 on Fedora 36.

Executing the following T-SQL (extracted from mssql-jdbc):

DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (id INT, name VARCHAR(42));

DECLARE @mssqljdbc_temp_sp_columns_result TABLE(
    TABLE_QUALIFIER SYSNAME,
    TABLE_OWNER SYSNAME,
    TABLE_NAME SYSNAME,
    COLUMN_NAME SYSNAME,
    DATA_TYPE SMALLINT,
    TYPE_NAME SYSNAME,
    PRECISION INT,
    LENGTH INT,
    SCALE SMALLINT,
    RADIX SMALLINT,
    NULLABLE SMALLINT,
    REMARKS VARCHAR(254),
    COLUMN_DEF NVARCHAR(4000),
    SQL_DATA_TYPE SMALLINT,
    SQL_DATETIME_SUB SMALLINT,
    CHAR_OCTET_LENGTH INT,
    ORDINAL_POSITION INT,
    IS_NULLABLE VARCHAR(254),
    SS_IS_SPARSE SMALLINT,
    SS_IS_COLUMN_SET SMALLINT,
    SS_IS_COMPUTED SMALLINT,
    SS_IS_IDENTITY SMALLINT, 
    SS_UDT_CATALOG_NAME NVARCHAR(128),
    SS_UDT_SCHEMA_NAME NVARCHAR(128),
    SS_UDT_ASSEMBLY_TYPE_NAME NVARCHAR(max),
    SS_XML_SCHEMACOLLECTION_CATALOG_NAME NVARCHAR(128),
    SS_XML_SCHEMACOLLECTION_SCHEMA_NAME NVARCHAR(128),
    SS_XML_SCHEMACOLLECTION_NAME NVARCHAR(128),
    SS_DATA_TYPE TINYINT
);

INSERT INTO @mssqljdbc_temp_sp_columns_result EXEC sp_columns_100 'test1';

SELECT * FROM @mssqljdbc_temp_sp_columns_result;

Gives the following error:

2023-01-03 22:28:24.217 GMT [57679] ERROR:  structure of query does not match function result type
2023-01-03 22:28:24.217 GMT [57679] DETAIL:  Returned type character varying does not match expected type "varchar"(254) in column 12.
2023-01-03 22:28:24.217 GMT [57679] CONTEXT:  PL/tsql function sp_columns_100(nvarchar,nvarchar,nvarchar,nvarchar,integer,integer,smallint) line 3 at SQL statement
	SQL statement "INSERT INTO "@mssqljdbc_temp_sp_columns_result" EXEC sp_columns_100 'test1';"
	PL/tsql function inline_code_block line 36 at SQL statement
	TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Writing Done Token
2023-01-03 22:28:24.217 GMT [57679] LOG:  Unmapped error found. Code: 67141764, Message: structure of query does not match function result type, File: attmap.c, Line: 119, Context: TDS

The problematic column number 12 here is REMARKS VARCHAR(254). If its definition in @mssqljdbc_temp_sp_columns_result is changed to REMARKS VARCHAR(MAX), then the error message is the following:

2023-01-03 22:28:39.121 GMT [57679] ERROR:  structure of query does not match function result type
2023-01-03 22:28:39.121 GMT [57679] DETAIL:  Returned type character varying does not match expected type "varchar" in column 12.

The initial T-SQL script passes correctly (and returns correct results) if I redefine the sp_columns_100 (add casts to required data types) by executing the following (connecting into postgres session on 5432 port):

CREATE OR REPLACE PROCEDURE sys.sp_columns_100 (
    "@table_name" sys.nvarchar(384),
    "@table_owner" sys.nvarchar(384) = '', 
    "@table_qualifier" sys.nvarchar(384) = '',
    "@column_name" sys.nvarchar(384) = '',
    "@namescope" int = 0,
    "@odbcver" int = 2,
    "@fusepattern" smallint = 1)
AS $$
BEGIN
	select out_table_qualifier as TABLE_QUALIFIER, 
			out_table_owner as TABLE_OWNER,
			out_table_name as TABLE_NAME,
			out_column_name as COLUMN_NAME,
			out_data_type as DATA_TYPE,
			out_type_name as TYPE_NAME,
			out_precision as PRECISION,
			out_length as LENGTH,
			out_scale as SCALE,
			out_radix as RADIX,
			out_nullable as NULLABLE,
			cast(out_remarks as varchar(254)) as REMARKS,
			cast(out_column_def as nvarchar(4000)) as COLUMN_DEF,
			out_sql_data_type as SQL_DATA_TYPE,
			out_sql_datetime_sub as SQL_DATETIME_SUB,
			out_char_octet_length as CHAR_OCTET_LENGTH,
			out_ordinal_position as ORDINAL_POSITION,
			cast(out_is_nullable as varchar(254)) as IS_NULLABLE,
			out_ss_is_sparse as SS_IS_SPARSE,
			out_ss_is_column_set as SS_IS_COLUMN_SET,
			out_ss_is_computed as SS_IS_COMPUTED,
			out_ss_is_identity as SS_IS_IDENTITY,
			cast(out_ss_udt_catalog_name as nvarchar(128)) as SS_UDT_CATALOG_NAME,
			cast(out_ss_udt_schema_name as nvarchar(128)) as SS_UDT_SCHEMA_NAME,
			cast(out_ss_udt_assembly_type_name as nvarchar(max)) as SS_UDT_ASSEMBLY_TYPE_NAME,
			cast(out_ss_xml_schemacollection_catalog_name as nvarchar(128)) as SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
			cast(out_ss_xml_schemacollection_schema_name as nvarchar(128)) as SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
			cast(out_ss_xml_schemacollection_name as nvarchar(128)) as SS_XML_SCHEMACOLLECTION_NAME,
			cast((
			CASE
				WHEN out_ss_is_identity = 1 AND out_sql_data_type = -6 THEN 48 -- Tinyint Identity
				WHEN out_ss_is_identity = 1 AND out_sql_data_type = 5 THEN 52 -- Smallint Identity
				WHEN out_ss_is_identity = 1 AND out_sql_data_type = 4 THEN 56 -- Int Identity
				WHEN out_ss_is_identity = 1 AND out_sql_data_type = -5 THEN 63 -- Bigint Identity
				WHEN out_ss_is_identity = 1 AND out_sql_data_type = 3 THEN 55 -- Decimal Identity
				WHEN out_ss_is_identity = 1 AND out_sql_data_type = 2 THEN 63 -- Numeric Identity
				ELSE out_ss_data_type
			END
			) as tinyint) as SS_DATA_TYPE
	from sys.sp_columns_100_internal(sys.babelfish_truncate_identifier(@table_name),
		sys.babelfish_truncate_identifier(@table_owner),
		sys.babelfish_truncate_identifier(@table_qualifier),
		sys.babelfish_truncate_identifier(@column_name), @NameScope, @ODBCVer, @fusepattern)
END
$$
LANGUAGE 'pltsql';
GRANT ALL on PROCEDURE sys.sp_columns_100 TO PUBLIC;

Note 1: the scripts above were run after a fresh initdb using SQuirreL SQL editor with mssql-jdbc 8.2.2 and postgresql-42.4.0 drivers.

Note 2: this problem is also reproducible by calling JDBC method getColumns on mssql-jdbc driver.

Version

BABEL_1_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

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

[kuntalghosh]

This is a known issue with INSERT..EXEC statements. Here's an easier scenario to reproduce the issue:

1> create table t1(a varchar(20))
2> go
1> insert into t1 exec('SELECT ''hello''')
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
structure of query does not match function result type

We're working on a fix.

[staticlibs]

Apparently this was fixed some time ago in PR #2017 , closing the issue.