[Bug]: min function on NCHAR requires explicit casting

[scoquelin]

What happened?

We are trying to migrate existing code/queries from MS SQL Server to Babelfish and some queries involving MIN function and NCHAR fields won't work unless we resort to use explicit casting to VARCHAR. Is it intended behavior ?

Steps to reproduce :

• Create table and data

-- Create the SalesData table
CREATE TABLE SalesData (
    Product NCHAR(50),
    Year INT,
    Sales INT
);

-- Insert sample data into SalesData table
INSERT INTO SalesData (Product, Year, Sales)
VALUES 
    ('Apple', 2023, 100),
    ('Orange', 2023, 150),
    ('Apple', 2024, 120),
    ('Orange', 2024, 130);

This query works with MS SQL Server but NOT with Babelfish select min(Product) as Product from SalesData;

Msg 33557097, Level 16, State 1, Line 21 The string size for the given CHAR/NCHAR data is not defined. Please use an explicit CAST or CONVERT to CHAR(n)/NCHAR(n)

This is the workaround with explicit casting to make the query work with Babelfish select min(CAST(Product as VARCHAR(50))) as Product from SalesData;

Is there any way to make the query work without resorting to explicit casting ? Thanks for your feedback!

Version

BABEL_4_X_DEV

Extension

babelfishpg_tsql (Default)

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

No response

Relevant log output

2025-03-21 01:09:20.288 UTC [102] LOG:  Unmapped error found. Code: 130, Message: The string size for the given CHAR/NCHAR data is not defined. Please use an explicit CAST or CONVERT to CHAR(n)/NCHAR(n), File: tdsresponse.c, Line: 1691, Context: babelfishpg_tsql
2025-03-21 01:09:20.288 UTC [102] CONTEXT:  PL/tsql function inline_code_block line 21 at SQL statement
        TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Preparing to Send Back the Tds response
2025-03-21 01:09:20.288 UTC [102] LOG:  Unmapped error found. Code: 130, Message: The string size for the given CHAR/NCHAR data is not defined. Please use an explicit CAST or CONVERT to CHAR(n)/NCHAR(n), File: tdsresponse.c, Line: 1691, Context: babelfishpg_tsql
2025-03-21 01:09:20.288 UTC [102] CONTEXT:  TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Preparing to Send Back the Tds response
2025-03-21 01:09:20.288 UTC [102] ERROR:  The string size for the given CHAR/NCHAR data is not defined. Please use an explicit CAST or CONVERT to CHAR(n)/NCHAR(n)
2025-03-21 01:09:20.288 UTC [102] CONTEXT:  PL/tsql function inline_code_block line 21 at SQL statement
        TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Preparing to Send Back the Tds response
2025-03-21 01:09:20.288 UTC [102] LOG:  Unmapped error found. Code: 130, Message: The string size for the given CHAR/NCHAR data is not defined. Please use an explicit CAST or CONVERT to CHAR(n)/NCHAR(n), File: tdsresponse.c, Line: 1691, Context: TDS

Code of Conduct

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

[jsudrik]

Looks like Babelfish logic can not figure out the typemod information for the result and has a typmod of -1 at this point -

#0 TdsGetGenericTypmod (expr=0x9560f58) at src/backend/tds/tdsresponse.c:1836 #1 0x00007f53258d1a06 in PrepareRowDescription (typeinfo=0x9570138, plannedstmt=0x92881c0, targetlist=0x92883e0, formats=0x0, extendedInfo=false, fetchPkeys=false) at src/backend/tds/tdsresponse.c:2049 #2 0x00007f53258d35db in TdsSendRowDescription (typeinfo=0x9570138, plannedstmt=0x92881c0, targetlist=0x92883e0, formats=0x0) at src/backend/tds/tdsresponse.c:2912 #3 0x00007f53258bfaa2 in TdsPrinttupStartup (self=0x9562d28, operation=1, typeinfo=0x9570138) at src/backend/tds/tdsprinttup.c:87 #4 0x000000000075d5b5 in standard_ExecutorRun (queryDesc=0x93f1d10, direction=ForwardScanDirection, count=2147483647, execute_once=true) at execMain.c:357

Its the same for other functions, e.g. max().

This will need some work internally. This works in SQL Server and PostgreSQL.

We will create a bug for this.

Thanks for reporting.