Home Dashboard Directory Help
Search

= NULL in check constraint by Vidar Nordnes


Status: 

Closed
 as Won't Fix Help for as Won't Fix


2
0
Sign in
to vote
Type: Bug
ID: 478176
Opened: 7/29/2009 6:20:51 AM
Access Restriction: Public
0
Workaround(s)
view
5
User(s) can reproduce this bug

Description

If you have a check constraint with field1 = NULL, this will allow ANY input.
I know = NULL isn't valid. It should of course be IS NULL, but a developer where I work had written "Field1 IN (NULL, 'a')" in the check constraints expression window in management studio. When you click ok there, it changes it to "Field1 = NULL OR Field1 = 'a'"

The users were of course able to put in what ever they wanted in this field

I know IN is the same as =, so NULL shouldn't be written like this, but the bug here is that Field1 = NULL (which should return false), actually is handled as true if used in a check constraint
Details
Sign in to post a comment.
Posted by Microsoft on 7/31/2009 at 1:28 PM
Hi,
Thanks for your feedback. We could do check for field1 = NULL for example but I am not sure it is the right thing to do. What if it is something like field1 + dbo.fn() = NULL? Do we analyze dbo.fn() return value also? What if it is expression on the left hand side? Database engine should provide primitives and it is up to the developer to write the correct expresssions. We cannot presume the intent and perform validations in some specific scenarios.
Additionally, it is unclear to what extent we need to validate. For example, someone might code a check constraint with expression (1=0). Do you see that as valid or not? Maybe it is an interim expression added by the programmer or it is indeed what he / she wants. Similarly, you can write an expression like col IS NOT NULL even if the column is declared as NOT NULL. Do you make that invalid or not?
I have resolved this as "won't fix" since I don't see us adding specific checks like this to the product. If you leverage some of the unit testing features in tools like Visual Studio Database Edition, you can catch errors like this and make changes as necessary.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.