[Bug]: REGRESSION Comparing a bit value in a CASE statement to assign a CHAR type results in error

[bill-ramos-rmoswi]

What happened?

In testing Visual Studio 2022 with the SQL Data Tools to deploy a database project to Babelfish, the Publish action has a query like this to get the table types.

select object_id as id,
null as id2,
case
when [is_filetable] = 1 then'UF'
else [type]
end as [type],
schema_name(schema_id) as name1,
name as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.tables
where
is_ms_shipped != 1

This results in the following error:

Msg 33557097, Level 16, State 1, Line 27
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 test case can be simplified with the following code.

declare @type char(2) = 'UF';
declare @test bit = 0;
declare @testvalue bit = 1;
select case when @testvalue = 1 then 'BR'
	else @type
end as type;

This code works correctly in WiltonDB based on the Dec 2023 release of Babelfish.

In looking at the release notes, I noticed several fixes in 4.1 and 4.2 that could have caused this problem.

This is a blocking bug for Caylent for our new tool!

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

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

No response

Relevant log output

No response

Code of Conduct

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

[rohit01010]

Thanks @bill-ramos-rmoswi for reporting this issue. Please note that we are already tracking this issue and will take it up soon.

[staticlibs]

I believe this was fixed recently by #2931.