News for hackers. Stuff that matters.
Re:The Meaning of Null in Databases and Programming Language... by klodolph on Thursday April 30, 2020 @01:15PM (#23033306)
This can’t be correct.

At least in Rust, null (or None) is not treated at all like how databases treat it. If you have T: PartialEq<T>, then there’s an implementation for Option<T>: PartialEq<Option<T>>.

With this implementation, None == None. The result is true. None != None is false.

In an SQL database, (null = null) is null. Neither true nor false. (null != null) is also null. Neither true nor false.

This is an enormous difference, and it is basically THE gotcha for working with null in SQL databases.

If you want to translate that into a "traditional" programming language, the closest I can give you is Haskell, where you can think of SQL equality as being normal equality lifted into the Maybe applicative functor. (If that doesn’t make sense, you’re not a Haskell programmer, don’t worry about it.)

    sqlEq :: (Applicative f, Eq a) => f a -> f a -> f Bool
    sqlEq = liftA2 (==)
This has a generalization of the tri-value semantics.

    > Nothing `sqlEq` Nothing
    Nothing
    > Nothing `sqlEq` Just 4
    Nothing
    > Just 4 `sqlEq` Just 5
    Just False
    > Just 4 `sqlEq` Just 4
    Just True
I say “generalization” because this works in any applicative functor.
Re:The Meaning of Null in Databases and Programming Language... by perl4ever on Thursday April 30, 2020 @07:51PM (#23037641)
Null in SQL databases is more complicated than that.

Quote from Oracle documentation: "Unless otherwise stated, group functions ignore NULL values"

So, sometimes operations on nulls don't produce null, and in fact that's considered normal...except when it isn't.

Re:The Meaning of Null in Databases and Programming Language... by vivekseth on Thursday April 30, 2020 @01:50PM (#23033757)
Thanks for the explanation, I forgot that null != null in databases. I was thinking more about how null != 0 and is a separate concept in languages with optional values. You’re right that optionals in these languages don’t quite work the same as in databases
Re:The Meaning of Null in Databases and Programming Language... by Sniffnoy on Thursday April 30, 2020 @02:29PM (#23034301)
But it's not NULL != NULL. It's that NULL == NULL is NULL. That's different! NUll != NULL is also NULL, for instance.
Re:The Meaning of Null in Databases and Programming Language... by vivekseth on Thursday April 30, 2020 @02:35PM (#23034397)
Maybe a concise way to describe it is that in databases NULL represents the absence of a value. Since NULL is not a value, operators like == and != don't really make sense on NULL, and also produce NULL as an output.