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

11–20 of 45 posts

Re: Nulls Nullified (2005)

#11

Post by cubefox »

Mikhail_Edoshin wrote: post #7
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…

As far as I understand NULLs were indeed a pragmatic choice, but the chief reason was the need to compute derived table values. E.g. in a join it is normal to get unknown values for a cell. This has to be expressed somehow. How? NULL seems to be a reasonably good generic solution to this. It may be possible to come up with a different generic solution, but such a solution would probably require something like conditi…

I wonder how Codd's original relational algebra handled this case, as he didn't have NULL cells.

Re: Nulls Nullified (2005)

#12

Post by cubefox »

MrJohz wrote: post #8
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)

Typescript does have full unions, although only partially discriminated ones (in the sense that `number | string` is discriminated but `number | number` is not - custom discriminators can be written fairly easily though). This resolves the problem that the previous poster had - nullability is not some magic trait of certain types, but rather a function of the general case of union types. In that case, `string | null`…

What does being "discriminated" mean here?

Re: Nulls Nullified (2005)

#13

Post by SPBS »

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 that many people also complain about and grumble that it should have been unified into one.

2. There's no reason for NULL to not equal NULL, which makes NULL a headache to handle and requires special IS NULL/IS NOT NULL operators to test for NULL-ness.

- Postgres 15 now addresses this with the DDL option UNIQUE NULLS NOT DISTINCT.

Finally, the author claims to have a solution that solves all these problems but sadly locked it behind a paywall on a website which is now defunct.

> Until recently, there was no logically correct, relational solution to missing data. We offer an outline of a possible such a solution in Practical Database Foundations paper #8, “The Final NULL in the Coffin,” which also summarizes the problems with NULLs.

http://www.dbdebunk.citymax.com/page/page/1396241.htm

> This website has been cancelled.

> Click here to go to the 5-minute website builder.

https://web.archive.org/web/20041209115415/http://www.dbdebu...

> ORDERING AND PRICING

> Delivery will be in PDF format. We strongly recommend to upgrade to the latest version of Acrobat reader and to have the following fonts installed: Verdana, Courier, Arial Narrow, Arial Black and Wingdings, so we don't have to embed these fonts and enlarge the files.

Re: Nulls Nullified (2005)

#14

Post by cubefox »

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…

While I'm a big proponent of union types in programming languages (against Haskell-like Option wrapping and unwrapping), it is not clear to me that they would work for something like SQL. Would it mean allowing columns with union types?

Re: Nulls Nullified (2005)

#15

Post by Mikhail_Edoshin »

cubefox wrote: post #11
Mikhail_Edoshin wrote: post #7

Earlier quoted context omitted.

As far as I understand NULLs were indeed a pragmatic choice, but the chief reason was the need to compute derived table values. E.g. in a join it is normal to get unknown values for a cell. This has to be expressed somehow. How? NULL seems to be a reasonably good generic solution to this. It may be possible to come up with a different generic solution, but such a solution would probably require something like conditi…

I wonder how Codd's original relational algebra handled this case, as he didn't have NULL cells.

This part I do not know, but NULLs are either Codd’s idea or the one he sided with.

Another example when an invention goes against the established logic would be quaternions.

Re: Nulls Nullified (2005)

#16

Post by Mikhail_Edoshin »

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…

It seems to be available for $14.99 on Amazon.

Re: Nulls Nullified (2005)

#17

Post by at_compile_time »

Mikhail_Edoshin wrote: post #15
cubefox wrote: post #11

Earlier quoted context omitted.

I wonder how Codd's original relational algebra handled this case, as he didn't have NULL cells.

This part I do not know, but NULLs are either Codd’s idea or the one he sided with. Another example when an invention goes against the established logic would be quaternions.

>Another example when an invention goes against the established logic would be quaternions.

How so?

Re: Nulls Nullified (2005)

#18

Post by bazoom42 »

cubefox wrote: post #11
Mikhail_Edoshin wrote: post #7

Earlier quoted context omitted.

As far as I understand NULLs were indeed a pragmatic choice, but the chief reason was the need to compute derived table values. E.g. in a join it is normal to get unknown values for a cell. This has to be expressed somehow. How? NULL seems to be a reasonably good generic solution to this. It may be possible to come up with a different generic solution, but such a solution would probably require something like conditi…

I wonder how Codd's original relational algebra handled this case, as he didn't have NULL cells.

I don’t think it had outer joins.

Re: Nulls Nullified (2005)

#19

Post by bazoom42 »

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…

As soon as you have composite types, you will want to filter and join on the individual components, which would require extending the query language to allow drilling down into nested composite types. Basically you are back to hieracical databases. This is why Codd recommends expressing composite types as relations rather than complex values. You can express the same information, but can use relational algebra rather then special-case operators.

Nulls are a special case though - even if null was not allowed in base tables, you would still need them in outer joins.

Re: Nulls Nullified (2005)

#20

Post by bazoom42 »

dtech wrote: post #4
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/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. Not so in SQL, since one unknown value is not necessaily the same as some other unknown value.

Post reply on HN