Hacker News Reader

Comment by datadrivenangel | Hacker News Reader

SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

View on HN

Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.

The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. The use of x = NULL instead of x IS NULL is pretty much always a mistake.

More importantly, x = value instead of (x = value and x IS NOT NULL) is almost always a mistake, and a stupidly subtle one at that. And for this curse, we get… nothing particularly useful from these semantics.

Also the x != NULL case is completely cursed

> The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra.

It's a three-valued logic (though not trinary, which would use a base-3 number system) in a three-valued algebra: specifically, the relational algebra. The outcome of a logical test has three values: true, false, or NULL; this is distinct from Boole's algebra where outcomes have a continuous value between 0 and 1 inclusive.

That's not the only time it is weird. There's even a whole book by one of the pioneers of the relational DB model, Date's "Database Technology: Nulls Considered Harmful" [1], covering many of the ways it is weird.

[1] https://www.amazon.com/Database-Technology-Nulls-Considered-...

> SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

Could you explain how this makes sense then?

  SELECT ...
  WHERE NULL

If NULL is just "unknown" then shouldn't this be a type error?

Moreover, could you explain why the heck this ought to be empty?

  WITH T AS (SELECT 1 AS C1,
             NULL AS C2)
    SELECT C1, C2
    FROM T
    INNER JOIN T
    USING (C1, C2);

As a human this looks insane to me, "relational algebra" be damned. You find a row, then you look it up again, and it's not there? What the hell?

Agreed.

I will die on the hill that regular C-like nulls are the actual thing that's weird.

The real billion dollar mistake [1] was the damage it made on the minds of developers.

[1] https://en.wikipedia.org/wiki/Tony_Hoare

We should start adjusting that billion for inflation.

Even null in programming languages isn't so bad if it's a distinct type. The problem with null in languages like Java is that null is part of every reference type (C's pointers are another world of broken, null being basically just another unsafe invalid address).

Most languages nowadays do get nulls right, even PHP of all things.

>unsafe invalid address

Ironically NULL is probably the safest pointer value in C, as any dereferences from it (and thousands of pages that follow it) are guaranteed to crash.

Well, that's a problem on many of the devices I've seen: zero is a valid memory address and dereferencing it does not cause any kind of crash. In fact some hardware requires reading or maybe even writing to that address. In an age of virtual memory there's no reason why zero should cause a crash and it wastes an entire page of memory for every application to make that happen, if it does.

I haven't had the chance to work on any MMU-less devices, but I don't quite follow your remark about wasting a page. Crashing is just the default behaviour in the absence of a mapping and consumes no resources.

The granularity of virtual address mapping is usually a page. On many systems, that's 4 kilobytes of address space. In order to trigger a fault when the address 0x0000000000000000 is dereferenced, it's necessary to map the entire address range from 0x0000000000000000 to 0x0000000000000fff to the same faulting behaviour.

That's a waste of a page.

Yeah but there is no "page" there. Assuming a process starts with an empty page table (meaning every single address will segfault), you don't have to do anything else to get the crashing behaviour for null.

Unless you're talking about some kind of device which supports virtual memory, but also by default sets up mappings, including at 0 which seems weird to me.

So if I have an empty page table 0x00000001 can be referenced as a valid address?

Memory mapping on all hardware with which I am familiar (which is not a small integer) is done by pages. If you have to fault on one address in a page (eg. zero page, stack canaries, heap guards) you have to fault on every single address in that page. But "memory is cheap". Until it's not.

Any system where losing one page would be too dear is likely not using a MMU in the first place.

Besides, memory access is twice as fast on the zero page! Wait, you're telling me the 6502 isn't the standard anymore? ;p

Reply on news.ycombinator.com