Hacker News Reader

Comment by vivekseth | Hacker News Reader

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

View on HN

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.

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

But it's not NULL != NULL. It's that NULL == NULL is NULL. That's different! NUll != NULL is also NULL, for instance.

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.

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.

I'm not a C programmer, rather an extensive database user. And NULL == 0 seems awkward and nonsensical to me.

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.

Fun reading on what systems didn’t use 0 for NULL: https://stackoverflow.com/questions/2597142/when-was-the-nul...

Looking at popular SQL databases (you know who you are!), NULL == '' seems pretty awkward to me, too!

Those languages use three value logic?

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

Reply on news.ycombinator.com