Add support for new SQL Server 2022 "IS [NOT] DISTINCT FROM" TSQL language enhancement.

[mcenzato]

I've updated the package to the last version 161.8901.0 but still the new Sql 2022 TSQL syntax "IS [NOT] DISTINCT FROM" is not reconized and results in a syntax error. SSMS 19.1 recognizes this, older versions don't. Try this statment: it works fine if executed but fails in parsing using the package:

DECLARE @i1 int = null;
DECLARE @i2 int = 10;
DECLARE @i3 int = 10;
DECLARE @i4 int = null;
SELECT 
	Test1 = CONCAT('@i1 ', IIF(@i1 IS DISTINCT FROM @i2, '!=', '=='), ' @i2'),
	Test2 = CONCAT('@i1 ', IIF(@i1 IS DISTINCT FROM @i3, '!=', '=='), ' @i3'),
	Test3 = CONCAT('@i1 ', IIF(@i1 IS DISTINCT FROM @i4, '!=', '=='), ' @i4'),
	Test4 = CONCAT('@i2 ', IIF(@i2 IS DISTINCT FROM @i3, '!=', '=='), ' @i3'),
	Test5 = CONCAT('@i2 ', IIF(@i2 IS DISTINCT FROM @i4, '!=', '=='), ' @i4'),
	Test6 = CONCAT('@i3 ', IIF(@i3 IS DISTINCT FROM @i4, '!=', '=='), ' @i4')

[clement911]

Any plans of supporting this. This is becoming an increasingly used features.

[dhiraj-n]

Been a while since this issue has been opened. Is there any plan on adding support for this?

[DanJary]

Is there any update on a decision on this one? My understanding is that it is dependent on Microsoft?

[dzsquared]

Apologies I don't have the version this landed in on hand, but I just tried it out in 170.128.0 (current) and the script parses with the 160 parser. I've tried the example above and

SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [Customer] 
WHERE  [PhoneNumber] IS NOT DISTINCT FROM [AlternatePhoneNumber];

both aren't throwing errors for me. Can someone double check/correct me on this? thanks!

[clement911]

Confirming that we have been successfully parsing IS [NOT] DISTINCT FROM expressions for a while. It definitely works with 170.128.0

[clement911]

@mcenzato how do you construct the parser? We use new TSql170Parser(true, SqlEngineType.SqlAzure) and I wonder if you might be using an older TSQL version, where the DISTINCT FROM construct is not suppored??