force order of conditions executions -- where clause

Question

Is there any way to force order of conditions executions (in where clause) I want force mssql filter rows ISJSON(Data)=1 first, than JSON_VALUE... later

I have this query:

SELECT *,
       ISJSON(Data) p
FROM dbo.Table
WHERE  ISJSON(Data)  = 1  AND JSON_VALUE(Data, '$.F1.F2') IS NOT NULL;

and I got error for rows without valid JSON in 'data' column

JSON text is not properly formatted. Unexpected character '.' is found at position 12.

I have tried to filter the rows in child query, but didn't help, because queryplan changes the order of execution:

SELECT *,
       ISJSON(Data) p
FROM
(SELECT * FROM dbo.Table WHERE ISJSON(Data) = 1) q
WHERE JSON_VALUE(Data, '$.F1.F2') IS NOT NULL;

a temporary solution is :

SELECT *,
       ISJSON(Data) p
FROM
(SELECT *,IIF(ISJSON(Data) =1,Data,'{}') JData FROM dbo.Table WHERE ISJSON(Data) = 1) q
WHERE JSON_VALUE(JData, '$.F1.F2') IS NOT NULL;

but is not an optimal solution

Answer 1

Remember this one fact: The server is free to reorder WHERE and JOIN conditions as it sees fit, including pushing through subqueries. The semantics must stay the same, but the server never takes into account possible runtime errors in those semantics, only logical and relational semantics.

So your first attempt at using a subquery isn't anything more than a fluke, and may break at any time.

The only way to guarantee that reordering won't happen is to use a CASE expression or derivatives such as NULLIF and IIF. (And even then, you must only use scalar expressions, not aggregate functions.)

This is why your final option worked. It is the only guaranteed method. You can simplify it as follows:

SELECT *,
       ISJSON(Data) p
FROM dbo.Table
WHERE JSON_VALUE(CASE WHEN ISJSON(Data) = 1 THEN Data END, '$.F1.F2') IS NOT NULL;

Passing NULL to JSON_VALUE just gets you a NULL back, so this should always work.