Hacker News Reader

Comment by adamzochowski | Hacker News Reader

Sigh,

In all other programming languages the code has to be along the lines of:

    if ((jane.car.colour == james.car.colour) and
        (jane.car.colour != null            ) and 
        (           null != james.car.colour)):
       print ("James and Jane have same car color")

or attempted with a second variable to indicate if value is known

    if (( jane.car.colourKnown == True ) and 
        (james.car.colourKnown == True ) and 
        ( jane.car.colour == james.car.colour)):
       print ("James and Jane have same car colour")

I like to think that SQL nulls are the most human way. I think of a scenario: James and Jane each own a car. I don't know neither car colour. Does it mean they are of same colour? All typical programming languages say "Yes". SQL says "I can't tell you, I don't know", and that is what a human would say.

View on HN

Plenty of languages have types that let you ask those kind of operations in a nice way. E.g. in Haskell you'd do

    traverse (flip when (putStrLn "James and Jane have same car color")) $
        liftA2 == james.car.colour jane.car.colour

The problem isn't that SQL supports tri-state logic, it's that it gives you no way of opting out of it. Over 90% of the time a given value should never be null, but there's no way to put SQL into a mode where a null in a given intermediate stage is an error. Instead it will silently give you a strange answer with no indication that anything went wrong.

Then why on earth is NULL being treated the same as false for the purposes of a WHERE clause.

Simple point:

  SELECT 'foo' WHERE bar = baz FROM quux
  UNION ALL
  SELECT 'bar' WHERE bar <> baz FROM quux

To me, there are two acceptable outcomes of this situation:

1) The result is a single row containing "foo" or "bar".

2) The result is an error message that the truthiness of 'bar = baz' cannot be determined.

However, ansi-null-compatible-SQL just will give you an empty resultset if bar or baz are null. That's not the server saying "I don't know" to "bar = baz", that's the server saying "neither" which is very freaking different. "I don't know" is an acceptable answer. "neither" is a lie.

That is unacceptable, and I'm utterly bewildered that people can insist that this is okay.

It sounds like it is operating according to a closed-world assumption, in which this is reasonable behavior.

> Then why on earth is NULL being treated the same as false for the purposes of a WHERE clause.

Because, by definition, the WHERE clause returns a record when it evaluates to TRUE. FALSE and UNKNOWN are not TRUE.

Second, you absolutely can do what you're trying to here, but not the way you've done it:

  SELECT CASE WHEN bar = baz THEN 'foo' ELSE 'bar' END AS corge
  FROM quux

You're asking the wrong question and wondering why you're getting the wrong answer.

Your brain is going to explode like mine did when you learn about Option types[1].

[1] https://en.m.wikipedia.org/wiki/Option_type

This doesn't really solve GP's problem, as the Nothing type is generally still equal to itself. Certainly useful for general purpose programming languages, though.

The problem is really that you're using the wrong equality type here. You get the right semantics if you use `= : A -> A -> Bool` and then apply it to a `Maybe A` and a `Maybe A` by lifting it: you end up with `Nothing` if either input was `Nothing`, and `Just answer` if both were known.

For what it's worth, in your "all other programming languages" example you don't need the second null check

    null != james.car.colour

It's redundant when we know the first two are true.

Reply on news.ycombinator.com