[Bug]: Computed string column gives unmapped error

[micke314]

What happened?

Compute a string value for a computed column does not work.

create table T(C as N'X' + N'Y');

Result:

Msg 33557097, Level 16, State 1, Line 5
generation expression is not immutable

My guess is that the postgreSQL function concat is used and that one is not immutable. || is probably a better alternative.

Version

BABEL_5_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

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

Ubuntu (Default)

Relevant log output

2025-10-06 12:25:51.340 UTC [131] LOG:  Unmapped error found. Code: 117833860, Message: generation expression is not immutable, File: heap.c, Line: 2962, Context: babelfishpg_tsql
2025-10-06 12:25:51.340 UTC [131] CONTEXT:  PL/tsql function inline_code_block line 1 at SQL statement
        TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request
2025-10-06 12:25:51.340 UTC [131] LOG:  Unmapped error found. Code: 117833860, Message: generation expression is not immutable, File: heap.c, Line: 2962, Context: babelfishpg_tsql
2025-10-06 12:25:51.340 UTC [131] CONTEXT:  TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request
2025-10-06 12:25:51.340 UTC [131] ERROR:  generation expression is not immutable
2025-10-06 12:25:51.340 UTC [131] CONTEXT:  SQL statement "create table T(C as N'X' + N'Y');"
        PL/tsql function inline_code_block line 1 at SQL statement
        TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request
2025-10-06 12:25:51.340 UTC [131] LOG:  Unmapped error found. Code: 117833860, Message: generation expression is not immutable, File: heap.c, Line: 2962, Context: TDS
2025-10-06 12:25:51.340 UTC [131] CONTEXT:  TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request

Code of Conduct

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

[robverschoor]

This is a known issue, related to the result of certain expressions not being deterministic in SQL Server, which does not map well to PG. As a workaround, a trigger can be used instead.

[tanscorpio7]

@rohit01010 I am curious if system function concat could be marked immutable ?

[rohit01010]

@rohit01010 I am curious if system function concat could be marked immutable ?

@tanscorpio7 Yes, sys.concat should ideally be immutable, but currently sys.concat is dependent on PostgreSQL function ARRAY_TO_STRING which is a stable function. Hence we cannot directly mark sys.concat as immutable.