News for hackers. Stuff that matters.
Re:The Meaning of Null in Databases and Programming Language... by vivekseth on Thursday April 30, 2020 @12:13PM (#23032500)
Although this is relevant for languages like C where NULL == 0, other (typically more modern languages) treat Null similar to how databases treat it. Ex: Swift, Kotlin, Rust
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.
Re:The Meaning of Null in Databases and Programming Language... by oarabbus_ on Thursday April 30, 2020 @01:08PM (#23033233)
I'm not a C programmer, rather an extensive database user. And NULL == 0 seems awkward and nonsensical to me.
Re:The Meaning of Null in Databases and Programming Language... by klodolph on Thursday April 30, 2020 @01:27PM (#23033450)
It’s an awkward holdover from bygone days. If you go back far enough in time, before it was really “C”, there was no difference between pointers and integers. The shift happened gradually, with pointers turning into a different type. I suspect NULL was kept as 0 to avoid breaking existing code. Then you can just:

    #define NULL 0
This works in both C and C++. Conveniently, on most modern systems, a null pointer also points to memory address 0. So you can also do this:

    struct mystruct {
        int length;
        int *data;
    };
    struct mystruct x;
    memset(&x, 0, sizeof(x));
On modern systems this will initialize x.data to NULL. It just writes zero bytes into the structure, and since the NULL pointer is represented just by a bunch of zero bytes, you can conveniently zero-initialize structures with pointers this way (again, on modern systems). Strictly speaking, this is not portable, but it will only break on fairly esoteric systems.
Re:The Meaning of Null in Databases and Programming Language... by colejohnson66 on Friday May 01, 2020 @11:36AM (#23043096)
Fun reading on what systems didn’t use 0 for NULL: https://stackoverflow.com/questions/2597142/when-was-the-nul...
Re:The Meaning of Null in Databases and Programming Language... by ken on Friday May 01, 2020 @12:21AM (#23039096)
Looking at popular SQL databases (you know who you are!), NULL == '' seems pretty awkward to me, too!
Re:The Meaning of Null in Databases and Programming Language... by herdrick on Thursday April 30, 2020 @12:45PM (#23032953)
Those languages use three value logic?
Re:The Meaning of Null in Databases and Programming Language... by rovolo on Thursday April 30, 2020 @01:46PM (#23033693)
I think they mean that NULL is a part of the type system, rather than an overloaded value of the same type. You can't assign NULL to a Boolean, but you can assign NULL to a nullable Boolean. From the article:

> The programming language null is a value but database null is not a value.

Those languages can use three value logic, but only if the type is marked as nullable. Newer languages have syntactic sugar to make it easier to handle null, which means that most functions can be written assuming their inputs are non-null. SQL by contrast defines most functions to handle nullable types. Here are some examples in kotlin (all ? relate to nullability, see a tutorial for more info):

    val b : Boolean? = null
    b.and(true)
    // type error

    b?.and(true)
    // null

    (b ?: true).and(true)
    // true

    b?.let { bb -> true.and(bb) }
    // null