Why does this throw a JSON parsing error and why can't I trap it?

Question

Consider the below function which returns a portion of a well-formatted JSON object. It will return "Bad JSON" when the JSON is not well-formatted. Importantly, it will not error when the JSON is not well-formatted.

CREATE OR ALTER FUNCTION TestJSONParse
(
    @nv nvarchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        v3 = isNull(t2.v2,t1.v1)
    FROM        (
                    SELECT
                        v1 = 'Bad JSON'
                ) AS t1
    LEFT JOIN   (
                    SELECT
                        v2 = JSON_VALUE(@nv,'$.prop')
                    WHERE ISJSON(@nv) <> 0
                ) AS t2
    ON          1 = 1
);
GO

SELECT * FROM TestJSONParse('{"prop": "Good JSON"}');
SELECT * FROM TestJSONParse('zzzz');
GO

Results:

v3
Good JSON

v3
Bad JSON

So far, so good.

Now I want to use this function in a procedure and feed it the same bad JSON:

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        SELECT
            @v = v3
        FROM        TestJSONParse(@nv);
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

EXEC TestJSONParseProc
    @nv = N'zzzz';
GO

Results:

Msg 13609, Level 16, State 2, Procedure TestJSONParseProc, Line 10 [Batch Start Line 45]
JSON text is not properly formatted. Unexpected character 'z' is found at position 0.

1. Why does this error occur? The evaluation of JSON_VALUE should not take place because ISJSON should be 0 and therefore the row is filtered from the results.

2. Why isn't this error caught by the BEGIN TRY?

I found a suggestion online that this was some sort of a compilation error. Some support for that conclusion is provided by the behavior when the first call of the procedure after creation is with valid JSON:

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        SELECT
            @v = v3
        FROM        TestJSONParse(@nv);
        SELECT v3 = @v;
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

EXEC TestJSONParseProc
    @nv = N'{"prop": "Good JSON"}';

EXEC TestJSONParseProc
    @nv = N'zzzz';
GO

Results:

v3
Good JSON

v3
Bad JSON

Can anyone confirm that the reason this happens and why it can't be caught is because what's really happening is that SQL Server is evaluating the expression as part of the compilation process and therefore crashes before the error handling ever has a chance to work? (If so, is there any justification for this behavior?)

Or is there some other explanation?

For reference, @@VERSION returns:

Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Feb 20 2021 17:51:58 
    Copyright (C) 2019 Microsoft Corporation

---

Update 2021-04-27:

Things which don't fix the issue:

(1) Wrapping the JSON_VALUE in a CASE statment:

CREATE OR ALTER FUNCTION TestJSONParse
(
    @nv nvarchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        v3 = isNull(t2.v2,t1.v1)
    FROM        (
                    SELECT
                        v1 = 'Bad JSON'
                ) AS t1
    LEFT JOIN   (
                    SELECT
                        v2 = CASE WHEN ISJSON(@nv) <> 0 THEN JSON_VALUE(@nv,'$.prop') ELSE NULL END
                    WHERE ISJSON(@nv) <> 0
                ) AS t2
    ON          1 = 1
);
GO

(2a) "Touching" the parameter value to make it "different" - ISNULL

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        SET @nv = isNull(@nv,N'');
        SET @nv2 = @nv;
        SELECT
            @v = v3
        FROM        TestJSONParse(@nv);
        SELECT v3 = @v;
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

(2b) "Touching" the parameter value to make it "different" - concatenate empty string

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        SET @nv = @nv + N'';
        SELECT
            @v = v3
        FROM        TestJSONParse(@nv);
        SELECT v3 = @v;
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

(3) Wrapping entire function call in ISJSON check

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        IF ISJSON(@nv) <> 0
        BEGIN
            SELECT
                @v = v3
            FROM        TestJSONParse(@nv);
        END
        ELSE
        BEGIN
            RAISERROR('Bad JSON',15,1);
        END
        SELECT v3 = @v;
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

Declaring a new variable in the proc and using that does, however, fix the issue:

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        DECLARE @nv2 nvarchar(max);
        SET @nv2 = @nv;
        SELECT
            @v = v3
        FROM        TestJSONParse(@nv2);
        SELECT v3 = @v;
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

That also bolsters the case that it's some sort of parameter sniffing compilation issue.

---

Update 2021-04-28:

As Mikael Eriksson suggested in a comment, using OPTIMIZE FOR @nv = NULL works as well. As noted, this also bolsters the case for this being a compile-time issue.

If a set of valid objects which would run successfully won't compile because of how the optimizer evaluates them, I would argue that's a bug in the optimizer/compiler.

CREATE OR ALTER PROCEDURE TestJSONParseProc
(
    @nv nvarchar(max)
)
AS
BEGIN
    BEGIN TRY
        DECLARE @v nvarchar(max);
        SELECT
            @v = v3
        FROM        TestJSONParse(@nv)
        OPTION      (OPTIMIZE FOR (@nv = NULL));
        SELECT v3 = @v;
    END TRY
    BEGIN CATCH
        SELECT
            Result = N'I have caught an error.';
    END CATCH
END
GO

Answer 1

You need to use the ISJSON() function to verify the column is valid JSON in the SELECT clause regardless of the WHERE clause. The reasoning for this is because contrary to popular belief, sometimes the SQL Engine generates an execution plan where it finds it more efficient to evaluate the SELECT clause before the WHERE clause is applied. That results in these odd cases where an exception is thrown unbeknownst to the average developer why.

So inside your TestJSONParse() function you'll need to change the line of code v2 = JSON_VALUE(@nv,'$.prop') to v2 = CASE WHEN ISJSON(@nv) = 1 THEN JSON_VALUE(@nv,'$.prop') ELSE NULL, for example.

Answer 2

This appears to be a bug in SQL Server's compilation/optimization system.

A feedback item has been submitted:

https://feedback.azure.com/d365community/idea/072ac3a7-5825-ec11-b6e6-000d3a4f0da0

JSON Parsing Error during Compilation / Optimization

Invalid JSON in a parameter can cause an error during the compilation/optimization phase, bypassing any TRY/CATCH logic. The only way around the issue is to use query hints like OPTIMIZE FOR, use an internal variable, or to run through valid JSON the first time the procedure is called.