{"id":78560,"date":"2018-04-30T13:25:55","date_gmt":"2018-04-30T13:25:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=78560"},"modified":"2018-04-30T13:25:55","modified_gmt":"2018-04-30T13:25:55","slug":"operator-precedence-versus-confusing-constraint-translation","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/operator-precedence-versus-confusing-constraint-translation\/","title":{"rendered":"Operator Precedence versus the Confusing Constraint Translation"},"content":{"rendered":"

The other day, a person I work with was creating some fairly complex CHECK constraints to protect data in a new table that was being created (a practice I am 100% for: https:\/\/www.red-gate.com\/simple-talk\/blogs\/eliminating-annoying-space-characters-via-check-constraints\/<\/a>. The multiple constraints were combined into just one, with several conditions condensed into a single Boolean expression, using several ANDs, ORs, and parentheticals. When the constraint was being reviewed, the scripted output of the constraint definition looked funny, as the parenthesis had all been removed.<\/p>\n

You can see the same thing in the following table, which I will add a semi complex CHECK constraint, with similar use of parentheses, with a NOT thrown in to help the example be a bit more complex:<\/p>\n

USE tempdb;\r\nGO\r\nCREATE SCHEMA Demo;\r\nGO\r\nCREATE TABLE Demo.ExampleConstraint\r\n(\r\n ExampleConstraintId int NOT NULL \r\n CONSTRAINT PK_ExampleConstraint PRIMARY KEY,\r\n Value1 int NULL,\r\n Value2 int NULL\r\n);<\/pre>\n
Then add this CHECK constraint:<\/p>\n
ALTER TABLE Demo.ExampleConstraint\r\n ADD CONSTRAINT CHKExampleConstraint\r\n CHECK (\r\n        (Value1 IS NOT NULL AND Value2 IS NULL)\r\n         OR\r\n        (Value1 IS NULL AND Value2 IS NOT NULL)\r\n         OR NOT Value1 = Value2 AND Value2 > 2 * Value1\r\n        );<\/pre>\n
Now, script out the constraint, or just use the definition you can get from the sys.check_constraint catalog view (something that would be really nice to have on the sys.foreign_keys table):<\/p>\n
SELECT definition\r\nFROM  sys.check_constraints\r\nWHERE OBJECT_SCHEMA_NAME(parent_object_id) = 'Demo'\r\n  AND OBJECT_NAME(parent_object_id) = 'ExampleConstraint';<\/pre>\n
You will see the code is not the same as when I coded it:<\/p>\n
([Value1] IS NOT NULL AND [Value2] IS NULL OR [Value1] IS NULL AND \r\n     [Value2] IS NOT NULL OR NOT [Value1]=[Value2] AND [Value2]>(2)*[Value1])<\/pre>\n
Looking as this, one thing stands out to the eye: the parenthesis have been removed from the comparisons, but added to the literal value of 2. (Also, everything is surrounded by square brackets, but as awful as that looks, it is a common thing to see in code that is generated\/parsed, such as this or a query plan.) When the person who wrote the similar code saw this, they were concerned it was wrong, then the next person who did a code review was also concerned it was wrong, and when I saw it, I was sure it was right, but only because I had a bit more faith that if this were a problem with SQL Server’s toolset, I would probably have read about it!<\/p>\n
Personally, I had to really look at the code, test out a similar expression in a CASE expression, and read this web page to remind me why it was actually right: Microsoft Docs: Operator Precedence<\/a>. I mean, who writes code like this?<\/p>\n
It all has to do with operator precedence, the operators in the expression are processed in the following order of:<\/p>\n
1. Comparison Operators come first. So the IS NOT NULL, =, and > will be evaluated. So if Value1 = 2, and Value2 = NULL:<\/p>\n
([Value1] IS NOT NULL AND [Value2] IS NULL OR [Value1] IS NULL AND [Value2] IS NOT NULL OR NOT [Value1]=[Value2] AND [Value2]>(2)*[Value1])\r\n       \\        \/               \\   \/               \\   \/                 \\      \/                  \\     \/               \\      \/\r\n(        TRUE         AND       TRUE       OR       FALSE      AND        FALSE         OR NOT      UNKNOWN      AND       UNKNOWN )<\/pre>\n
Each of the comparisons returns a Boolean value, or UNKNOWN, which means a NULL was compared with something, not using a NULL safe comparison, like IS NOT NULL.<\/p>\n
 Now, here is where you instinctively start to think like simple arithmetic operations. 1 + 2 – 3\u00a0 equals 0, no matter if you do 1 + 2 first, or 2 – 3 first. But <this> AND <that> OR <the other thing> is not the same if you do the AND or the OR comparison first, for sure.\u00a0<\/p>\n
2. NOT\u00a0<\/p>\n
We have one NOT operator, and NOT (UNKNOWN) still equals UNKNOWN (one of my favorite problems to talk about, not actually deal with in code!)<\/p>\n
( TRUE AND TRUE OR FALSE AND FALSE OR NOT UNKNOWN AND UNKNOWN )\r\n                                         \\   \/\r\n( TRUE AND TRUE OR FALSE AND FALSE OR   UNKNOWN   AND UNKNOWN )<\/pre>\n
3. AND<\/p>\n
( TRUE AND TRUE OR FALSE AND FALSE OR UNKNOWN AND UNKNOWN )\r\n      \\     \/         \\      \/            \\         \/\r\n( \u00a0 \u00a0  TRUE \u00a0 \u00a0 OR \u00a0 \u00a0 FALSE \u00a0 \u00a0 \u00a0 OR \u00a0 \u00a0 \u00a0 UNKNOWN \u00a0 \u00a0 \u00a0 )<\/pre>\n
4. OR<\/p>\n
Which you can see is going to be false:<\/p>\n
( TRUE OR FALSE OR UNKNNOWN )<\/pre>\n
So the values provided will obviously work, since TRUE OR anything will be TRUE. And of course, as long as one value is NULL and the other is NOT NULL, then the first two conditions will be met, and the last will not matter.<\/p>\n
If both numbers are NOT NULL, then the first two conditions will FALSE, and the OR NOT Value1=Value2 AND Value2 > 2 * Value1 would actually matter. So if value2 is more than 2 times the size of value1, then the data would pass the CHECK constraint’s conditions.<\/p>\n
Looking at this, you may think this is a good enough constraint, but it isn’t if you care about the case where both columns are NULL, then the output will be UNKNOWN OR UNKNOWN OR UNKNOWN. A constraint fails on a FALSE return value only. So:<\/p>\n
INSERT Demo.ExampleConstraint(ExampleConstraintId, Value1, Value2)\r\nVALUES(1, NULL, NULL);<\/pre>\n
This means that the first two checks for the column values to have one NULL and one NOT NULL values is ineffective since both values being NULL will also succeed. If you want to make sure that exactly either 1 of the two columns is not null, you would need something like<\/p>\n
--These parens ARE required in the parsed expression\r\n(<original expression> )\r\n\u00a0 AND (Value1 IS NOT NULL OR Value2 IS NOT NULL)\u00a0<\/pre>\n
But the constraint will actually matter if both values are NOT NULL:<\/p>\n
INSERT Demo.ExampleConstraint(ExampleConstraintId, Value1, Value2)\r\nVALUES(2, 1, 1);<\/pre>\n
Will not, because NOT Value1=Value2 AND Value2 > 2 * Value1 requires that Value1<>Value2<\/p>\n
So while no one I know would have written the code this way, strictly because it is hard to read (minimally, <\/span>formatting the code somewhat would give visual clues that the writer knew that AND has higher precedence than OR:<\/p>\n
([Value1] IS NOT NULL AND [Value2] IS NULL OR [Value1] IS NULL \r\n      AND [Value2] IS NOT NULL OR NOT [Value1]=[Value2] AND [Value2]>(2)*[Value1])<\/pre>\n
It is exactly the same Boolean expression as:<\/p>\n
(\r\n (Value1 IS NOT NULL AND Value2 IS NULL)\r\n OR\r\n (Value1 IS NULL AND Value2 IS NOT NULL)\r\n OR NOT Value1 = Value2 AND Value2 > 2 * Value1\r\n )<\/pre>\n
In reality, had I been writing this expression, I would have been really likely to have put more parentheses in the expression, such as:<\/p>\n
(\r\n   (Value1 IS NOT NULL AND Value2 IS NULL)\r\n    OR\r\n   (Value1 IS NULL AND Value2 IS NOT NULL)\r\n    OR \r\n   (NOT(Value1 = Value2) AND Value2 > 2 * Value1)\r\n )<\/pre>\n
Just for clarity of reading the expression. Either way it was a good reminder of operator precedence and how SQL Server applies it to the saving and scripting of constraints.<\/p>\n
Note:<\/em>
\n  In the expression: NOT [Value1]=[Value2] AND [Value2]>(2)*[Value1]; it probably seems like the Value1=Value2 is unnecessary for the [Value2]>(2)*[Value1] part, if value1 = -1 and value2 = -1, then the -1 > 2 * -1 will be true.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
The other day, a person I work with was creating some fairly complex CHECK constraints to protect data in a new table that was being created (a practice I am 100% for: https:\/\/www.red-gate.com\/simple-talk\/blogs\/eliminating-annoying-space-characters-via-check-constraints\/. The multiple constraints were combined into just one, with several conditions condensed into a single Boolean expression, using several ANDs, ORs, and……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4546,4183,4252],"coauthors":[],"class_list":["post-78560","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-check-contraints","tag-t-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/78560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=78560"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/78560\/revisions"}],"predecessor-version":[{"id":79386,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/78560\/revisions\/79386"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=78560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=78560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=78560"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=78560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}