It seems that to checkmy credit report I need to answer a question that even I may not be allowed to know!

Fortunately leaving the answer blank was accepted

 I'm surprised it even said "What is".  Normally that part changes with the specific question.

protip: null = null

Vechni:

protip: null = null

Not in SQL.

ammoQ:

Vechni:

protip: null = null

Not in SQL.

ammoQ:

Vechni:

protip: null = null

 

Not in SQL.

SET ANSI_NULLS OFF FTW!

Welbog:

Yeah, but why the fuck not? I hate languages in which you need special checks for null since null != null. I hate it when I have a test like foo = bar and another test like foo != bar and encounter conditions when they're both false. Fucking shit, foo = bar OR foo != bar should be a tautology, damn it!

Agreed.  It's even more annoying when you have to do "IS NULL" or "IS NOT NULL" all over the place. 

Welbog:

Yeah, but why the fuck not? I hate languages in which you need special checks for null since null != null. I hate it when I have a test like foo = bar and another test like foo != bar and encounter conditions when they're both false. Fucking shit, foo = bar OR foo != bar should be a tautology, damn it!

Ostensibly it's because null is not a value, it's the absence of a value and therefore does not have any known datatype.  Thus, comparing null to anything is always undefined, even when comparing to null.  If you have an Int variable that's NULL, and a VarChar(50) variable that's also null, should they test positive for equality?

Personally, I think the developers were just lazy and decided they could get away with writing a blanket "return null" instead of actually finding a solution to the datatype issue.

Welbog:

Fucking shit, foo = bar OR foo != bar should be a tautology, damn it!

GettinSadda:

Notice: Phorm, and it's agents including ISPs collecting data on Phorm's behalf, are specifically forbidden from performing any processing or monitoring of the content of the above post. Hence, under the Regulation of Investigatory Powers Act 2000 any such attempt to profile this page by Phorm or it's agents is illegal.

Phight the power.

Welbog:

ammoQ:

Vechni:

protip: null = null

Not in SQL.

Yeah, but why the fuck not? I hate languages in which you need special checks for null since null != null. I hate it when I have a test like foo = bar and another test like foo != bar and encounter conditions when they're both false. Fucking shit, foo = bar OR foo != bar should be a tautology, damn it!

A rather intelligent and skilled DBA once explained to me why NULL == NULL isn't true in ANSI SQL.  The confusion arises from what people think NULL means.

The vast majority of computer programming languages, and by extension the vast majority of computer programmers, say: null == no value.

SQL, however, says: null == unknown value.

Once you grasp that concept, things start making a lot more sense.

NULL == NULL => "Some unknown value is equal to some other unknown value" => NULL (it is unknown whether or not this is true).

 A + NULL => "Some number plus an unknown value" => NULL ("unknown value")

A=A AND NULL => "A true thing and an unknown thing are both true" => NULL ("unknown")

A=A OR NULL => "A true thing is true OR an unknown thing is true" => TRUE

Welbog:

foo = bar OR foo != bar should be a tautology, damn it!

 Not in three-predicate logic.

 Where most people fail to understand Nulls is their use of misleading language. The term "null value" is bad because there is no such thing as a "null value". The better term is "null marker".

If a column in a database contains a null marker then that means that the database does not know what the value of that attribute is at this point in time. The attribute does have a value. For example, consider a table storing details about people. One of the rows has a null marker in the height column. Does that mean the person in question does not have a height? No, it doesn't. That person does have a height, its just that the database doesn't know it right now.

Imagine pushing your hand into a jar of lollies and grabbing a handful. You have no idea how many lollies are in your clenched fist (i.e. a null marker). If you compare your clenched fist with an open hand full of lollies (so you know how many there are) and say "are the number of lollies in these two hands the same?" then the answer has to be null. ( A = Null --> Null ). If you ask "is the number of lollies in this hand plus 5 equal to 10?" then the answer has to be null. ( Null+5 > 10 --> Null ). If you grab an unknown number of lollies in both hands and ask "are the number of lollies in these two hands the same?" then the answer has to be null. ( Null = Null --> Null ).

In my opinion, the real WTF is the use of Null in programming languages. How would the value of a variable be unknown to the program? I can understand "Uninitialised" but I don't understand how a variable can be unknown. Is the CPU unable to follow a pointer and interrogate the bit of memory it is pointing to?

Null markers are required in databases (thanks to Codd's rules and the realities of storing information on real-world entities) but not in programming languages.

B

havokk:

Not in three-predicate logic.

Circular argument -- obviously Welpog is criticizing the use of 3 value logic for relational databases.

havokk:

If a column in a database contains a null marker then that means that the database does not know what the value of that attribute is at this point in time. The attribute does have a value.

No it doesn't.  That is one particular meaning of NULL but it can also indicate that a value is not applicable.  In fact, there are multiple actual uses of NULL which means that its definition is not internally consistent.  Meanwhile, in the real world this adds a bunch of complexity to relational databases that is of very little use to business applications.  If you actually have a need for these various types of NULL you can just implement magic values to represent unknown, unknowable or inapplicable values.  This would not burden the majority of developers with dealing with NULL while still providing the possibility of rolling your own multi-value logic system.  Additionally, the magic values would actually be indexed whereas NULLs frequently are not.

havokk:

In my opinion, the real WTF is the use of Null in programming languages. How would the value of a variable be unknown to the program? I can understand "Uninitialised" but I don't understand how a variable can be unknown. Is the CPU unable to follow a pointer and interrogate the bit of memory it is pointing to?

This is completely silly.  For one, you're ignoring uses of NULL other than unknown.  Essentially, though, nulls in programming languages can be treated the same as NULLs in SQL.  You seem to be confusing the meaning of null within the abstract language and the actual implementation in hardware.

havokk:

Null markers are required in databases (thanks to Codd's rules and the realities of storing information on real-world entities) but not in programming languages.

Nonsense.  NULLs can be represented any number of ways.  The thing is, there is very little reason to make three value logic the default for databases.  Multi-value logic can be implemented on top of a standard boolean system.  Yes, it's more work than having it enshrined in the language standard, but it's more work for people who are using three state logic who are in the minority.  Most of the time two state logic is all that is needed and forcing developers to mess with all three value logic just to write business apps is absurd. 

morbiuswilters:

Essentially, though, nulls in programming languages can be treated the same as NULLs in SQL. 

Not really. For example, in java, (variables pointing to) objects are compared like that: o1.equals(o2). If o1 happens to be null, this crashes, so you need to check for null, too.

morbiuswilters:

That is one particular meaning of NULL but it can also indicate that a value is not applicable. 

Good point. Codd requires that nulls be supported for "missing information and inapplicable information". I argue that "inapplicable" means you haven't normalised your tables enough.

A null marker in a column could indicate many things. For example, a null marker in a telephone column in a person table could mean (1) this person has no phone, or (2) I don't know if this person has a phone or not, or (3) I know this person does have a phone but I don't know what the number is, or (4) I don't care if this person has a phone. If an app needs to distinguish between these meanings then the database design should accomodate it.

I'm not sure I agree or disagree with "of very little use to business applications". I believe that supporting unknown and inapplicable is simply "required for business applications in the real world" and we have to deal with it. We are not always going to know every attribute of the entities we are modelling.

Magic numbers are not necessarily the way to deal with unknown data. Imagine using $-1.00 to indicate "unknown price". What implication does this now have on averages and totals? What implication does this have on comparing prices? What implication does this have on tax calculations?

Regardless of how they are dealt with, we have to write more code to deal with these exceptional cases.

morbiuswilters:

Essentially, though, nulls in programming languages can be treated the same as NULLs in SQL.  You seem to be confusing the meaning of null within the abstract language and the actual implementation in hardware.

Not at all. A null pointer, a pointer that is not currently pointing to anything, is not "unknown or inapplicable". It is a known state. Maybe we should be calling it something other than "null" to avoid the confusion? An unitialised variable, one that has been created but not assigned any value, is not "unknown or inapplicable", it is logically unavailable. We should not be using the term "null" for this, we should be using the correct term "uninitialised".

Three predicate logic (true, false, null) is not really anything specific to logic - nulls apply to all data types so they apply to boolean data types.

B

ammoQ:

o1.equals(o2). If o1 happens to be null, this crashes, so you need to check for null, too.

Based on what havokk said, I'd say he's someone who has trouble differentiating between meaning and rules. Logic has certain natural and intuitive features that are very important to preserve. One of those is operation reversibility, i.e. A op B is equivalent to not (A notop B) where op is the operation in question and notop is its opposite. This equivalence doesn't hold in SQL when nulls are involved and that's really fucking confusing the first few times you have to deal with it because it doesn't make any goddamned sense. Even after you deal with it, you still have to write special cases in situations you'd expect to just work.

Like say you want to get all rows whose "city" field isn't "Denver". SELECT * FROM Doug WHERE City <> 'Denver' doesn't return any rows in which City is null. So you have to add a OR City IS NULL part to it. And then if you want to parameterize the thing to accept any criterion for the City field in place of Denver, you basically have to write a whole different clause in the case that the parameter you passed is null, since WHERE City <> NULL returns false for every field, and the OR City IS NULL part makes the thing return every null-City row, which is the opposite of what you want!

I don't know what kind of intuition drives this kind of insanity. I don't think I want to know.

Welbog:

Your comparison operations are reversible in Java, but not in SQL.

Precisely.  This is a limitation of any multi-value logic system.  That doesn't mean such systems aren't useful but to have it be the default logic model of SQL is pretty absurd.

Welbog:

I don't know what kind of intuition drives this kind of insanity. I don't think I want to know.

It's not insanity or a matter of intuition.  It's the fact that one special case for values is enshrined in the standard and is required for interaction with the data.  NULL isn't special in and of itself, it just represents an unknown, unknowable or inapplicable quantity.  Why not have other special values like FILE_NOT_FOUND?  The problem is that it forces the methods of edge-cases into the main program flow which is more complex.  Complexity is the enemy of efficiency, verifiability, validity and maintainability.

havokk:

I argue that "inapplicable" means you haven't normalised your tables enough.

And any software engineer will tell you that normalization is sometimes the enemy of simplicity and performance.  The relational model is all well and good in theory but in practical terms it is sometimes limited.

havokk:

If an app needs to distinguish between these meanings then the database design should accomodate it.

Yes, the database design, not the query language.

havokk:

I'm not sure I agree or disagree with "of very little use to business applications". I believe that supporting unknown and inapplicable is simply "required for business applications in the real world" and we have to deal with it. We are not always going to know every attribute of the entities we are modelling.

You're misunderstanding.  Unknown and inapplicable are certainly useful, but enshrining one particular case in the language is what I am arguing against.

havokk:

Magic numbers are not necessarily the way to deal with unknown data. Imagine using $-1.00 to indicate "unknown price". What implication does this now have on averages and totals? What implication does this have on comparing prices? What implication does this have on tax calculations?

Regardless of how they are dealt with, we have to write more code to deal with these exceptional cases.

SELECT avg(total) FROM table WHERE (total >= 0).  You have to write more code anyway to deal with NULL cases (IS NOT NULL).  True, functions like avg() are smart enough to ignore NULLs, but in and of itself that is a small benefit.  Meanwhile, addition, concatenation and comparison are all different anyway.  NULL itself is nothing special, it's just a magic value that has its own keyword.  The problem is that it changes the fundamental logic system in use which has far-reaching consequences that are confusing for new developers and annoying to experienced ones.

havokk:

Not at all. A null pointer, a pointer that is not currently pointing to anything, is not "unknown or inapplicable". It is a known state.

null is anything you want it to be.  It's essentially just another value that can be used to represent whatever is convenient.  The difference is that SQL pretty much forces you to deal with NULLs all over the place and most programming languages leave that up to the developer.  So in Java null can mean uninitialized, unknowable, unknown, inapplicable or even an error state.  It's all how you want to leverage that value.  You once again seem to be confusing the meaning of null in the higher-level language with what it is commonly used to represent.  Essentially it's just another magic value but most programming languages do not enforce the strict "NULL does not equal anything else" idiom that is found in SQL.  Those languages maintain the simplicity and ease of boolean logic while still allowing the developer to define whatever magic values he would like for the data types in use.  What's more, those magic values can be sensitive to the context of the data type which helps even more.

havokk:

Three predicate logic (true, false, null) is not really anything specific to logic - nulls apply to all data types so they apply to boolean data types.

NULLs don't always apply to data types.  In my experience, NULL values are the extreme exception which makes it annoying that SQL is modeled around them. 

Welbog:

Like say you want to get all rows whose "city" field isn't "Denver". SELECT * FROM Doug WHERE City <> 'Denver' doesn't return any rows in which City is null. So you have to add a OR City IS NULL part to it. And then if you want to parameterize the thing to accept any criterion for the City field in place of Denver, you basically have to write a whole different clause in the case that the parameter you passed is null, since WHERE City <> NULL returns false for every field, and the OR City IS NULL part makes the thing return every null-City row, which is the opposite of what you want!

This is probably close enough:

WHERE coalesce(City,'') <> coalesce(@City,'')

unless you actually care about the difference between '' and null.

Probably better is:

WHERE NOT (City = @City)

Welbog:

Like say you want to get all rows whose "city" field isn't "Denver". SELECT * FROM Doug WHERE City <> 'Denver' doesn't return any rows in which City is null. So you have to add a OR City IS NULL part to it. And then if you want to parameterize the thing to accept any criterion for the City field in place of Denver, you basically have to write a whole different clause in the case that the parameter you passed is null, since WHERE City <> NULL returns false for every field, and the OR City IS NULL part makes the thing return every null-City row, which is the opposite of what you want!

I don't know what kind of intuition drives this kind of insanity. I don't think I want to know.

What about the situation where, say, you have a person tables. You have incomplete data, say that locating people isn't the main purpose of your app. But anyway, you want to know all the people near Dave.

SELECT p2.name FROM person p1, person p2 WHERE p1.city = p2.city AND p1.name = 'Dave';

So what if We don't have a city field for Dave?

What is the correct answer here? We can't determin the correct answer.

What is definatly the wrong thing to do, is to return all the other people in the DB who we don't know where they live. That would be very broken.

In SQL, you're generally checking relationships between objects - joins and the like, which is why it uses a logic that is correct in these situations.

emurphy:

unless you actually care about the difference between '' and null.

In Oracle, there is no such difference, so such a statement would not be portable.

emurphy:

WHERE NOT (City = @City)

Wouldn't work in Oracle (and probably not in MSSQLServer either), since not(NULL='DEVER') = not(null) = null