5 ms·

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 !=

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.

stouset 6y ago
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 https://en.m.wikipedia.org/wiki/Option_type
- ericpauley 6y ago
  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.
  - Smaug123 6y ago
    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.
vimslayer 6y ago
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.
lmm 6y ago
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.
Pxtl 6y ago
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.
- atlantique 6y ago
  It sounds like it is operating according to a closed-world assumption, in which this is reasonable behavior.
- da_chicken 6y ago
  > 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.

stouset 6y ago

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 https://en.m.wikipedia.org/wiki/Option_type

ericpauley 6y ago
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.
- Smaug123 6y ago
  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.

vimslayer 6y ago

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.

lmm 6y ago

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.

Pxtl 6y ago

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.

atlantique 6y ago
It sounds like it is operating according to a closed-world assumption, in which this is reasonable behavior.
da_chicken 6y ago
> 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.