mssql 2022 - random incorrect syntax ')'. errors

Question

MSSQL Version

Microsoft SQL Server 2022 (RTM-CU2) (KB5023127) - 16.0.4015.1 (X64) 
	Feb 27 2023 15:40:01 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)

All database with compatibility_level=160

Sometimes when connecting the application via mssql-jdbc-9.4.1.jre8 errors "incorrect syntax ')'" appear, although the prepared query and procedure definition are correct.

Errors appear randomly. Stored Procedures can execute correctly several dozen times, and that an error appears in 2 or 3 subsequent executions. After that, the procedure works fine again (object definition hasn't changed)

The problem occurs on various procedures, but has never occurred with database compatibility_level=150.

Any ideas what could be causing the problems ?

Example:

Error caught by XE

StatementText5

       select
             c1.Table1Id
       from
             [dbo].[Table1] c1 with (nolock)
             join [dbo].[Table5] c5 with(nolock) on c1.ColEntId is null and c5.TableId = c1.ColEntElId and c5.UsrId = @usr and c5.Nb = @nb and c5.IsD = 0
       where
             (c5.Dest = 1 and c1.IsDel = 1 and c1.ModDate > @dt)
             or (c5.Dest = 0)
			 
       union
	   
       select
             c1.Table1Id
       from
             [dbo].[Table1] c1 with (nolock)
             join [dbo].[Table2] c2 with (nolock) on c2.Table2Id = c1.ColEntElId and c1.ColEntId = @Table1EntId
             join [dbo].[Table3] c3 with (nolock) on c2.Table3Id = c3.Table3Id
             join [dbo].[Table4] c4 with (nolock) on c4.Table4Id = c3.Table4Id
             join [dbo].[Table5] c5 with(nolock) on c5.TableId = c4.TableId and c5.UsrId = @usr and c5.Nb = @nb and c5.IsD = 0
       
       where
            (c5.Dest = 1 
				and ((c1.IsDel = 1 and c1.ModDate > @dt) or (c2.IsDel = 1 and c2.ModDate > @dt) or (c3.IsDel = 1 and c3.ModDate > @dt) or (c4.IsDel = 1 and c4.ModDate > @dt))
			)
            or (c5.Dest = 0)

Procedure definition

create PROCEDURE [schema].[StoredProcedure]
       @usr int, 
       @Nb varchar(100),
       @dt datetime
AS
BEGIN
       StatementText 3

       StatementText 4
       
       StatementText 5
       
END

Answer 1

Had you used the view in my article, you would have seen the statement where the error occurs. Although, the sql_handle is all zeroes here. But list_exceptions_sp will give you the full call stack.

But, hm, maybe this is related to PSP optimization where they dispatch a new query if a query is deemed to be subject to parameter-sniffing issues? To understand this, you would need to analyse the plan cache. But since you don't seem to disclose the code in public, my recommendation is that you open a case with CSS instead.

CU4 has other suprises

I was unaware of the incorrect-results bug. It's not clear to me, if this bug is new to CU4. The other issues with dumps every 15 minutes is not new to CU4, but I've seen reports of this bug earlier this year in Q&A. I did not know that PSPO had been identified as a culprit, though.

Answer 2

A few ideas to narrow down the problem., both for Greg and Chris. Can you try:

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF

in one of your databases where you have this problem, and see if you still get the the error? This would determine whether PSP optimisation is involved or not.

Also, can you use list_exceptions_sp and look at the statements on the call stack? It seems that the top frame has an empty SQL handle, but I'm curious on the one below. the sql_text element in the XML event is not of interesting when this a stored procedure call.

Can you see a common pattern? To add a leading question: is the last character always a right parentheses?

And for that matter, is the error message always exactly that: Incorrect syntax near ')'?

Answer 3

Unfortunately, after disabling PSPO Incorrect syntax near ')', errors still appear.

Answer 4

I've been in touch with a contact in the Product Group, and they certainly want to fix this bug. However, it is impossible for Microsoft, if they are not able to reproduce this error. Which, given the randomness of the error message, can be a little difficult.

I know that Greg has already opened a case, but it seems that it has not moved forward. I would also recommend that the others of you have an issue with this open a support case. And I would very much appreciate if you can share the case number. Either by posting it here, or mailing to me on esquel@sommarskog.se, so I can forward it to my contact.

Another alternative is file a bug on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 and share the link here. It sufficient that one of you file a bug, and then the rest of you can add comments to that bug. As long as you use a valid email address when you file the bug, Microsoft can contact you for details.

Answer 5

We are also running CU11 for one week now, and also in our environment the issued seems to be solved.