Unexpected order running OR operator on SQL Server 2019-CodePudding

I'm having a problem when migrating from SQL Server 2008 R2 to SQL Server 2019. My code

DECLARE @str NVARCHAR(50) = 'all',
        @int TINYINT = 1

DECLARE @tmp TABLE (val nvarchar(MAX))
INSERT INTO @tmp VALUES('123')
INSERT INTO @tmp VALUES('all')

SELECT val
FROM @tmp
WHERE @str = 'ALL' OR @int = val

When using SQL Server 2008 R2, it's fine. The output as expected like below

val
123
all

However, when I migrate to SQL Server 2019, the error like below occurs. Besides, it just happens unusually in 2019.

Msg 245 Level 16 State 1 Line 8 Conversion failed when converting the nvarchar value 'all' to data type int.

As you can see, the second condition OR @int = val happened unexpectedly.

I was wondering if it fails due to any breaking changes related to the order of OR operator or case sensitive ALL vs all in the next SQL Server 2008 R2 version.

Updated

Sorry for my reproduce code make you guys confuse. This is my original code

CodePudding user response：

You should do two of these three things:

(

Either use DECLARE @int nvarchar(max) = 1

OR
Use WHERE val = CONVERT(nvarchar(max), @int)

)

AND

Change to using STRING_SPLIT. That looping function is among the least efficient methods you could ever use to split strings, even before native solutions existed. See https://sqlblog.org/split

This db<>fiddle fiddle demonstrates.

And this one shows why WHERE @str = 'ALL' OR (@str <> 'ALL' AND @int = val) is not a solution. These patterns you're choosing only work if @str is always 'all', because they all break when it's anything else. So why have the OR at all?

You keep insisting that SQL Server should obey left to right evaluation, but we keep telling you that is simply not the case.

Here is an article by Bart Duncan at Microsoft, who worked on SQL Server, that you should absolutely read in full before posting any more comments or editing your question further. The critical point, though, is:

You cannot depend on expression evaluation order for things like

WHERE <expr1> OR <expr2>

since the optimizer might choose a plan that evaluates the second predicate before the first one.