Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Nulls Nullified (2005)

dbazine.com

Post reply on HN
Print view View original

21–30 of 45 posts

Re: Nulls Nullified (2005)

#21

Post by sacado2 »

quickthrower2 wrote: post #2

NULL makes little sense from first principles. Either get rid of it, or allow nulls (and logically any other union) by supporting union types. It is quite arbitrary to allow a type that makes sense (string, int) etc. and then also allow nulls so you allow INT | NULL union but no other unions. So you have this multi-purpose "other value" whose meaning is inferred by the application. I guess they were added as a pragma…

In theory NULL (or any such sentinel value, such as the infamous NaN is floatin-point arithmetic) makes sense because there are partially defined functions. Division being the most famous example, but most basic operations on lists share that property too.

The problem is the way it's handled in many programming languages, where it can blend so easily with legitimate value and blow the whole program off at execution time.

It's a practical problem, not a theoretical one.

And it's not the same as union types. The division of two numbers always returns a number, and nothing else, it's just that, for some values, it's not defined.

Re: Nulls Nullified (2005)

#22

Post by dspillett »

SPBS wrote: post #13
quickthrower2 wrote: post #2

NULL makes little sense from first principles. Either get rid of it, or allow nulls (and logically any other union) by supporting union types. It is quite arbitrary to allow a type that makes sense (string, int) etc. and then also allow nulls so you allow INT | NULL union but no other unions. So you have this multi-purpose "other value" whose meaning is inferred by the application. I guess they were added as a pragma…

> NULL makes little sense from first principles. You need something to represent lack of a value on LEFT JOINs. The author acknowledges as much, the two things he seems to be complaining about are: 1. It's impossible to distinguish between true lack of value from LEFT/RIGHT JOIN and a value that was directly set as NULL. - Interestingly, this is exactly why JavaScript has both null and undefined, a design decision th…

> There's no reason for NULL to not equal NULL

Only if you consider NULL to be an exact value, to have one meaning i.e. does not exist. In the mathematical set theory upon which relational database theory was built this is not the case.

NULL really represents “unknown” not just the subset that is “unknowable”/“does not exist” as which point not all NULLs are equal as they may represent entirely different things that you currently don't know - this is why NULL= == NULL (including NULL=NULL == NULL) and NULL≠ == NULL.

Re: Nulls Nullified (2005)

#23

Post by classified »

quickthrower2 wrote: post #2

NULL makes little sense from first principles. Either get rid of it, or allow nulls (and logically any other union) by supporting union types. It is quite arbitrary to allow a type that makes sense (string, int) etc. and then also allow nulls so you allow INT | NULL union but no other unions. So you have this multi-purpose "other value" whose meaning is inferred by the application. I guess they were added as a pragma…

It's not a union type, but an option. INT | NULL --> Option.

Re: Nulls Nullified (2005)

#24

Post by dspillett »

cubefox wrote: post #10

The author talks a lot about how NULL in SQL is supposedly unnecessary, and claims that it is totally possible to handle unknown SQL values without NULL. Yet he successfully avoids explaining how his solution is supposed to work. At the end he links to a (paid?) paper where his solution is apparently explained, though the link has since ceased to work. Great.

There are many papers describing how NULL as an explicit “value” (you still at least need something to present empty results from left or right joins) can be made unnecessary – just look back to the development of relational databases and the theory that are based upon. No need to pay for this author's paper when it probably just restates things that are found in many open access papers/books/other.

Representing unknown/non-existent without allowing an explicit NULL value is can be modelled by the property being another entity, you have a child table that lists the values of the property so where you would store a NULL otherwise there simply exists no row in that table. In SQL this means extra joins and in most (all?) SQL implementations this results in (sometimes significantly) lower performance. The big argument there is whether this points to a problem in the theory (if arguing that NULL should not be “stored”), in SQL as an implementation of relational database theory, or in the implementations of SQL…

[I'm aware my terminology is all over the place, as most people's is: when talking about relational theory rather than SQL as a speicific implementation of it I should use tuple not row, relation not table, etc., but getting that right only serves to confuse people (a great many of those with a more self-taught and/or field trained programming background background than one that involves any computer science study) who know only SQL and have thus far not needed to be aware of the theory or history].

Re: Nulls Nullified (2005)

#26

Post by dspillett »

bazoom42 wrote: post #20
dtech wrote: post #4

Earlier quoted context omitted.

NULL/missing is so common that it makes sense to provide special language support, even if you don't have full unions. See e.g. Kotlin and Typescript. What doesn't make sense is to treat a nullable type the same as non-nullable type causing errors everywhere (Java), or make it so special that it basically has completely separate logic and operators attached (SQL)

A lot of confusion comes from SQL nulls having the same name as nulls in programming languages. They are conceptually completely different things. Nulls in programming languages are a special “sentinel” value indicating the lack of an object. Nulls in sql means “unknown” or “I am not able to answer that question”. This is why in most programming languages two nulls are equal, since they are the same sentinel value. N…

Worse: with JSON NULL is considered a type not a value of other types, which can make validating nullable values with JSON-Schema a royal pain.

Re: Nulls Nullified (2005)

#27

Post by bazoom42 »

dspillett wrote: post #24
cubefox wrote: post #10

The author talks a lot about how NULL in SQL is supposedly unnecessary, and claims that it is totally possible to handle unknown SQL values without NULL. Yet he successfully avoids explaining how his solution is supposed to work. At the end he links to a (paid?) paper where his solution is apparently explained, though the link has since ceased to work. Great.

There are many papers describing how NULL as an explicit “value” (you still at least need something to present empty results from left or right joins) can be made unnecessary – just look back to the development of relational databases and the theory that are based upon. No need to pay for this author's paper when it probably just restates things that are found in many open access papers/books/other. Representing unkn…

But how to represent missing values in left/right/outer joins?

Re: Nulls Nullified (2005)

#28

Post by contravariant »

quickthrower2 wrote: post #2

NULL makes little sense from first principles. Either get rid of it, or allow nulls (and logically any other union) by supporting union types. It is quite arbitrary to allow a type that makes sense (string, int) etc. and then also allow nulls so you allow INT | NULL union but no other unions. So you have this multi-purpose "other value" whose meaning is inferred by the application. I guess they were added as a pragma…

Why do pointed sets not make sense from first principles?

Re: Nulls Nullified (2005)

#29

Post by tuyiown »

bazoom42 wrote: post #27
dspillett wrote: post #24

Earlier quoted context omitted.

There are many papers describing how NULL as an explicit “value” (you still at least need something to present empty results from left or right joins) can be made unnecessary – just look back to the development of relational databases and the theory that are based upon. No need to pay for this author's paper when it probably just restates things that are found in many open access papers/books/other. Representing unkn…

But how to represent missing values in left/right/outer joins?

I think this one of the many things that the author wanted to point out, outer joins is not a valid relational model operator, and thus, leads to generating nulls. Outer joins would have to be some kind of subqueries in relational model, which would likely cause either way too subtle querying semantics or, as little as the would, still always unacceptable performance cost, given how databases are benchmarked.

Re: Nulls Nullified (2005)

#30

Post by bazoom42 »

dspillett wrote: post #26
bazoom42 wrote: post #20

Earlier quoted context omitted.

A lot of confusion comes from SQL nulls having the same name as nulls in programming languages. They are conceptually completely different things. Nulls in programming languages are a special “sentinel” value indicating the lack of an object. Nulls in sql means “unknown” or “I am not able to answer that question”. This is why in most programming languages two nulls are equal, since they are the same sentinel value. N…

Worse: with JSON NULL is considered a type not a value of other types, which can make validating nullable values with JSON-Schema a royal pain.

JSON does not define a type system, but javascript defines null as the only value of the null type. This works fine in eg typescript since you can use a type union of null and any other type, if you want to indicate a nullable value.
Post reply on HN