[Bug]: float/real accept NaN/Infinity in Babelfish (but T-SQL doesn’t) — intended?

[bilalkah]

What happened?

In T-SQL, there’s no textual representation for non-finite floating values (NaN, Infinity, -Infinity). Attempts to cast such strings to float/real should fail. In Babelfish, the same inputs are accepted when targeting real (float4) or float (float8), because PostgreSQL’s float I/O routines allow these special values. Is this divergence intentional? If not, I can prepare a fix.

Case examples

SELECT CAST('NaN' AS float);
GO
SELECT CAST('Infinity' AS float);
GO
DECLARE @f FLOAT='infinify';
select @f;
GO

They all pass in babelfish and fail in T-SQL. Additionally, Also, if you connect babelfish via SSMS, they also fail. Probably SSMS does additional check as well.

Why this happens

As I see, sys.FLOAT and sys.REAL are domain types of float4 and float8, therefore when an inputs comes or when a cast happens they use underlying types behavior.

Result

So as a result, we can adjust the real/float behaviour with additional helper function in the following example for real datatype

Datum
varchar2float4(PG_FUNCTION_ARGS)
{
	VarChar    *source = PG_GETARG_VARCHAR_PP(0);
	char 	   *num;

	if (varcharTruelen(source) == 0)
		PG_RETURN_FLOAT4(0);

	num = varchar2cstring(source);

	/* Validate the input string for T-SQL float literals */
	if (!is_valid_tsql_float(num))
	{
		pfree(num);
		ereport(ERROR,
			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
			errmsg("Error converting data type varchar to real.")));
	}

	return cstring2float4(num);
}

If this divergence is intended, please confirm and consider documenting it as a known difference. Otherwise, I’m happy to submit a PR implementing the stricter T-SQL behavior described above. We can also introduce a GUC, default on:

babelfish.tsql_float_literals_strict = on

When off, keep current PostgreSQL behavior.

Version

BABEL_5_X_DEV (Default)

Extension

None

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

Ubuntu (Default)

Relevant log output

Code of Conduct

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

[Yvinayak07]

Nice finding. I don't think this divergence is intended. we should go ahead and fix this bug where we need to throw error for some special string literals.

[bilalkah]

Hi @Yvinayak07 Thank you for your reply, I have already started working on it. Let me share you the branch, I will open a PR soon.

https://github.com/bilalkah/babelfish_extensions/tree/bf_validate_tsql_float

[robverschoor]

This difference has not been intentional. Features that fail in T-SQL but do not fail in Babelfish have just not been a priority. For the SSMS failure, could you share the SSMS version where you see that happen?

[bilalkah]

SQL Server Management Studio 20.2.37.0

Let me add some examples and the results I got from SSMS.

declare @f float='infinity'
select @f
go

create table temp(float_number float);
go

insert into temp values ('1.0');
select * from temp;
go

insert into temp values ('infinity'),('NaN'),('inf');
select * from temp;
go

@f
----------------------
An error occurred while executing batch. Error message is: Arithmetic Overflow.

(1 row affected)
float_number
----------------------
1

(1 row affected)


(3 rows affected)
float_number
----------------------
1
An error occurred while executing batch. Error message is: Arithmetic Overflow.

Completion time: 2025-08-21T14:08:46.0750664+03:00

[robverschoor]

You wrote if you connect babelfish via SSMS, they also fail Could you elaborate a bit on how you are connecting? When I connect to BBF with SSMS v.18.12.1 through the object explorer, it works without error. If this same connection fails with SSMS v.20, that would be interesting.

[bilalkah]

Sorry, perhaps I could not explain the problem clearly. So, the issue is about setting/selecting special values to float variable such as 'infinity', 'NaN' etc.

• If you connect to BBF database via sqlcmd and run insert/select operation you get the special values returned. However, it is wrong behavior since T-SQL does not support this special values.
• If you connect to BBF database via SSMS and run the insert/select operations with the special values to FLOAT variable, then the query returns errors like https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/4028#issuecomment-3210083039. So, there is no SSMS connection failure, sorry for misunderstanding. The problem is that you can't set a special string to a float variable or select a row containing those special strings. I hope that's clear.

[robverschoor]

Thanks, it is clear.

[bilalkah]

Hi @robverschoor @Yvinayak07 I prepared the PR to fix this issue https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/4075 Can you please take a look at it and review? Thank you in advance!