SmartInt

ajj3085 posted on Wednesday, October 11, 2006

Hi all,

I have a SmartInt class and I know others do as well.

For sorting purposes, emptyIsMax is used to determine the empty value, which is int.MaxValue or int.MinValue.  That's fine.

What about when you use SmartInt.Value in a calculate and the SmartInt is empty?  It seems reasonable to me that Value should return 0 for this instance, since conceptually you could intereprete empty as 0.

1 + empty = 1.  Makes sense to me..

Or you could have the empty is more like a null...

1 + empty = empty.

Have have others handled this case?  I'd like to go the zero route, but just need to make sure I'm not going to regret that later...

Andy

jlazanowski replied on Wednesday, October 11, 2006

Andy,

I write a lot of reports, and I used to get frustrated with Crystal always returning a null value when I tried to add numbers that contained a null value, I expected a results of 1 + null to = 1 because I believed that Null should represent zero.

After fighting with this for a while, I asked around on the crystal forums to see if anyone could explain this behavior to me.  This turned out to be an interesting question, but it comes down to  relational algebra, and there is actually a rule for this situation

 Codd's Rule #3. Nulls are Treated Uniformly As Unknown

    * Null must always be interpreted as an unknown value
    * Null means no value has been entered; the value is not known
    * 'Unknown' is not the same thing as an empty string ("") or zero
    * EXAMPLE.  If you pick up an item in a store and the price is not marked, the price is unknown (NULL); it is not free
    * If not handled properly, nulls can cause confusion in your database
    * EXAMPLE. If you search for all of the authors whose home state is not California, the results will not include any authors with NULL in the  'state' column.  SQL is very literal. You asked for authors where the state was NOT California and NULL means 'unknown.'  A NULL value for 'state' may mean that the state is California and it may mean that it is not California; you just don't know.  Because the database engine can't tell for sure whether the state is not California, a record with NULL will not be returned
    *  Nulls propagate through arithmetic expressions (e.g., 2 + NULL = NULL)
    * Comparing a null to any value, including itself, returns NULL  

 

Now that doesn’t mean that you have to stick to this rule, but I think it's important to know that it exists and that some people (perhaps someone working on a project after you) would expect it to apply.

 

My .02

Justin

ajj3085 replied on Wednesday, October 11, 2006

Yes, agreed that there are good reasons for it.  I think I'll keep with this standard.  Theoretically in my particular case, the values should never been unknown.. but I'd like to handle it gracefully instead of one of my users finding out how to blank something they shoudn't be able to and my application acting odd... especially since this part of the app is our sales and invoicing system!

The example you give about searching for states is interesting.  Technically not knowing in which state the author resides is its own distinct value which isn't California, so I think you can make the argument the database should only filter rows where it is certain that the state isn't California.  Obviously unknown is not a certainty, and its also logical to include null state rows. 

In practice, I've written many applications which explicitly include those rows with null fields because people expect them to come up.  This is probably why some people say that nullable shouldn't be an option.  Its very very rare that in your application there's a difference between empty string and null (but you need a way to store a null date).

Andy