ISJSON constraints should not be escaped.

wpostma · February 23, 2016 2:08PM

Your BRACKET_ALL_THE_THINGS() function needs an EXCEPTION encoded so that JSON validation Constraints like this do not get rewritten:

ALTER TABLE [PHI].[Study] ADD CONSTRAINT [PHI_STUDY_ExtJSON_VALID] CHECK (isjson([ExtJSON])>0)

It appears that something inside SQL Source control is rewriting the above so that it breaks when executing, it writes the following BAD T-SQL instead:

ALTER TABLE [PHI].[Study] ADD CONSTRAINT [STUDY_ExtJSON_VALID] CHECK (([isjson]([ExtJSON])>(0)))

Look at that. the function isjson should NOT have [] around it, and the several extra pairs of parenthesis are pathological too.

Not EVERYTHING matching an [a-zA-Z]* regex inside a CHECK() expression can be assumed to be a literal field reference!

Probably you have some logic that tries to be smart about this, but the new JSON constraint functions in SQL 2016 are not one of the things you handle correctly.

Warren

Allen LeVan · February 24, 2016 5:18PM

Hello,

We've opened a ticket for this issue so we can troubleshoot further. I'll be responding to you through that ticket immediatly after this forum response, thanks.

wpostma · February 29, 2016 10:40AM

To reproduce you will need SQL server 2016 CTP 3.3. Create this table, and commit it into SQL Source control:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE &#91;dbo&#93;.&#91;Group&#93;(
	&#91;InternalGroupID&#93; &#91;BIGINT&#93; IDENTITY(1,1) NOT NULL,
	&#91;InternalRoleID&#93; &#91;BIGINT&#93; NOT NULL,
	&#91;GroupName&#93; &#91;NVARCHAR&#93;(64) NOT NULL,
	&#91;ExtJson&#93; &#91;NVARCHAR&#93;(MAX) NULL,
 CONSTRAINT &#91;PK_Group&#93; PRIMARY KEY CLUSTERED 
( &#91;InternalGroupID&#93; ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON &#91;PRIMARY&#93; ) ON &#91;PRIMARY&#93; TEXTIMAGE_ON &#91;PRIMARY&#93;

GO

After that commit, go to another working copy (another user's machine with SQL Source control) and try to fetch the changes and apply them.

After that, run this tsql to add the constraint which contains an expression in the form ( isjson([ExtJSON])> 0):

ALTER TABLE &#91;dbo&#93;.&#91;Group&#93;  WITH CHECK ADD  CONSTRAINT &#91;GROUP_ExtJSON_VALID&#93; CHECK  ( isjson(&#91;ExtJSON&#93;)&gt; 0)
GO

ALTER TABLE &#91;dbo&#93;.&#91;Group&#93; CHECK CONSTRAINT &#91;GROUP_ExtJSON_VALID&#93;
GO

Now commit that change on working copy on workstation 1. This commit should only contain the above extjson validation constraint and its check enable.

Now go back to workstation 2 and try to update.

Expectation:

ALTER TABLE [dbo].[Group] WITH CHECK ADD CONSTRAINT [GROUP_ExtJSON_VALID] CHECK ( isjson([ExtJSON])> 0)
GO

Actual:

ALTER TABLE [dbo].[Group] WITH CHECK ADD CONSTRAINT [GROUP_ExtJSON_VALID] CHECK ( [isjson]([ExtJSON])> 0)
GO

You can see an extra set of brackets.

wpostma · March 10, 2016 8:07AM

You know what would be great is if you open a ticket you post a link to that ticket here. I know. I'm smokin' weed right? Talking crazy?
Just an idea. Did some ticket get opened? Who knows.

Anyone with questions can contact me directly at wpostma at company dot com, and I work at a company called ramsoft.

ISJSON constraints should not be escaped.

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk