2

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
2
  • 1
    Instead of using a local variable as a workaround you could also do option (optimize for (@nv = null)) to the same effect. It do suggest that your own conclusion is correct that the error happens before exectution. Apr 28 at 6:32
  • I concur with you: it does appear to be a parameter sniffing bug. I suggest you file it on Azure Feedback Apr 29 at 0:29
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.

7
  • Thanks, but see update where that didn't work. Apr 28 at 2:58
  • @RileyMajor Are you 100% sure you accurately tested v2 = CASE WHEN ISJSON(@nv) = 1 THEN JSON_VALUE(@nv,'$.prop') ELSE NULL?...I've experienced this same issue (sans a JSON function but using other functions and operators) and the reasoning I mentioned was the exact issue occuring. Perhaps ensure you recompiled the TestJSONParseProc procedure after you updated the TestJSONParse() function? (It shouldn't matter, but for good measure.)
    – J.D.
    Apr 28 at 3:16
  • Yes, I did. in Azure SQL Database and SQL Server 2016 locally. Perhaps it's because the CASE function doesn't always save you from executing its innards inappropriately (sqlperformance.com/2014/06/t-sql-queries/…). Or perhaps because this is a compile-time issue so it's not actually executing and therefore doesn't respect the flow. (That would also explain why wrapping the whole function call in ISJSON doesn't help.) Apr 28 at 14:19
  • @RileyMajor Hmm... Wrapping the whole function call with ISJSON() is outside the scope of the SELECT clause where your issue is stemming from, so I understand why that wouldn't fix things. Do you receive the error message you provided in your post at compile time or runtime? If runtime then I don't think this is a compile time issue. But I do see your link on CASE statements not always covering the issue I described unfortunately. I think in my past experiences with this issue I was able to resolve this with using functions like TRY_CONVERT() instead, but I don't think JSON is an...
    – J.D.
    Apr 28 at 17:30
  • ...applicable data type for TRY_CONVERT() unfortunately. I'd have to think a little bit for your scenario and research if there's an alternative function you can use.
    – J.D.
    Apr 28 at 17:31
1

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

A feedback item has been submitted:

https://feedback.azure.com/forums/908035-sql-server/suggestions/43341234-json-parsing-error-during-compilation-optimizati

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.