Set simple parameterization for a json_value query (without using plan guides)

Question

I'm trying to run this query, but it fails it fails with:

The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.`

select  mt.pk           
from    My_Table mt    
where   json_value(mt.json_data, '$.group_id') != ''

As you can see, my parameter 2 is a string literal. The error is due a bug in SQL Server 2016, when forced parameterization is on for the database.

I have confirmed that forced parameterization is turned on in my database, using:

select name, is_parameterization_forced from sys.databases

I am generally aware that you can force simple parameterization by creating a plan guide, then using it for the query. However, reviewing this documentation, I think that may be too complicated for my actual situation. This is partly because in reality, these queries are be generated on the fly and it's not realistic to also generate plan guides for all of them.

Reviewing Query Hint docs, it says

The PARAMETERIZATION query hint can only be specified inside a plan guide. It cannot be specified directly within a query.

Upgrading to SQL Server 2017 is also not currently viable. Turning off forced parameterization for these databases is also not viable.

I am thinking that if I can set simple parameterization on for the query or session, that would be a workaround. Is such a technique possible? Is there any other workaround?

I realize that looking for solutions with simple parameterization is a little bit X/Y...any help is appreciated.

Paul White · Accepted Answer · 2021-08-08 16:38:41Z

So far I have collected the following workarounds:

Wrap the json_value function in a dummy subquery. For example,

 select  mt.pk    
 from    My_Table mt    
 where   (select json_value(mt.json_data, '$.group_id')) != ''

Per David's first suggestion, try "Optimize for Ad Hoc Workloads" instead of forced parameterization. I haven't tested this, but it's plausible since the problem is forced parameterization.
Per David's second suggestion, execute the SQL statement dynamically in another database, and reference the original db with 3 part naming. I have confirmed that this works. For example,
```
exec tempdb.sys.sp_executesql 
  N'select * from TargetDB.dbo.t where json_value(doc, ''$.group_id'') = '''' '
```

Going cross database is clever. It does work, even though it's a massive hack. What's even more bizarre is that apparently that had a connection level side effect of some kind. I'm able to run the original query normally with json_value afterwards, but only in that tab of SSMS. Copy/paste the identical SQL to another tab, errors per original description. Bizarre. But, it is a bug area after all.

Anything that defeats forced parameterization for the statement will serve as a workaround. The elements that prevent it are listed in the documentation. For example, adding OPTION (RECOMPILE), or introducing a test against a variable.

This bug is still not fixed in SQL Server 2016 SP2-CU17 build 13.0.5888.

Stack Exchange Network

Set simple parameterization for a json_value query (without using plan guides)

1 Answer 1

Your Answer

Hot Network Questions

Set simple parameterization for a json_value query (without using plan guides)

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions