4 ms·

This is great! I have multiple places in our code where I've had to define multiple unique indexes (with conditionals) to get around this. It's definitely confu

This is great! I have multiple places in our code where I've had to define multiple unique indexes (with conditionals) to get around this. It's definitely confusing behaviour the first time you encounter it. Honestly I think SQL already breaks it's own standard of "NULL = unknown". One example: If you do a LEFT OUTER JOIN and the joined data is missing, you get NULL in the resultset. But in that case, it's not "unknown" whether there are records that can be joined to, the answer is that there are definitively no records there. NULL is univerally considered by most programming languages to equal "not present" vs "unknown", and in many cases by SQL in practice. The ship has probably sailed on actually changing the default behaviour, but optional flags like these are a good step.

knorker 4y ago
NULL means "I don't have this data". It doesn't guarantee that the data doesn't exist anywhere. It's semantically ambiguous, yes. But it's not ambiguous that the database doesn't have the data. It's very clearly NULL. If "middle name" is NULL, then the database itself can't tell you if a person doesn't have a middle name, or if it's merely not yet been told the middle name. You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string.
- ryanbrunner 4y ago
  That's fair - as I said in another comment, it breaks down with keys. I can't think of a great way to model "this row definitively does not have whatever is referenced in another table". Using anything other than NULL for that means you can't use proper foreign keys.
- Pxtl 4y ago
  > You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string. This is what kills me. This seems like an obvious case where multiple flavors of NULL would make perfect sense. For example Ticket.CancellationDate isn't "unknown", it's this ticket is not cancelled. But SQL standard doesn't have a way to express "empty value" as distinct from "unknown value", so we're stuck working around the strange implications of using this square peg to fill a round hole. Oracle's bizarre "empty VARCHAR2s are NULL" is strange but is consistent with every other datatype that offers no way to say "empty" but "NULL". 0 is not the same as NULL int and is not the same as empty int. Mindate is not the same as NULL date and is not the same as empty date.
  - knorker 4y ago
    Well, for the string case empty string is… the empty string. Storing the exit code and stdout of a program run Oracle allows the state "it's not finished yet" to have exit code representation of NULL, but you cannot say the same for string. "Unknown" is not a perfect map to NULL. "I have no answer for you" is better. Maybe NULL values could use an annotation. But I think that would make things worse. Like let's say you have some query that joins addresses with coordinates. You know that semantically any address without a coordinate has an "unknown" coordinate, not that it authoritatively doesn't have coordinates. But not only do you need to change the data model then, but the query language too, to be able to express the difference where absense means one or the other. Now absense is NULL, and leaves it up to the application to interpret. SELECT a.address, b.coord FROM a LEFT OUTER JOIN (SELECT address,coord FROM coords WHERE is_test=FALSE) AS b ON a.address=b.address; Or maybe some coordinates are missing because their locations no longer exist. The house has been demolished, so it's not "unknown" where the house was, it's just that it no longer has a location. And how would you join that data. I don't think I've encountered a problem in practice with SQL in differentiating empty from unknown. It's out of scope of what SQL tries to solve, and that's fine.
SnowHill9902 4y ago
NULL means that the value is unknown, not that the data is unknown. You indeed have no data but its value may exist. You just don’t know it yet. NULL is very powerful when you understand that.
- ryanbrunner 4y ago
  That seems like a fairly big assumption on how I'm modelling the data though. Particularly in the LEFT JOIN example, I'm not sure if I agree with the idea of "a row not being present means that it's unknown whether it exists in the real world" depending on the underlying content being modelled. For a string column, you do have blanks vs. NULLs, so it's fairly easy to distinguish between "John definitely doesn't have a middle name" vs "It's unknown whether John has a middle name", but that doesn't hold true with most other datatypes.
  - SnowHill9902 4y ago
    The string ‘’ is as arbitrarily blank as 0 is a blank integer.
    - wongarsu 4y ago
      There are two "blank" integers: 0 is the identity element under addition, 1 is the identity element under multiplication. Or worded differently 0 is a blank addend, 1 is a blank multiplier. ‘’ would be the identity element for string concatenation, or any other string manipulation method I can think of, so it has a stronger claim on being blank than a 0 has. Unless your string is later parsed as something else. A blank JSON value would look differently from a blank phone number.
      - SnowHill9902 4y ago
        Sure, but it’s arbitrary from a data structure standpoint and sloppy design.
        4y ago

knorker 4y ago

NULL means "I don't have this data". It doesn't guarantee that the data doesn't exist anywhere. It's semantically ambiguous, yes. But it's not ambiguous that the database doesn't have the data. It's very clearly NULL. If "middle name" is NULL, then the database itself can't tell you if a person doesn't have a middle name, or if it's merely not yet been told the middle name. You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string.

ryanbrunner 4y ago
That's fair - as I said in another comment, it breaks down with keys. I can't think of a great way to model "this row definitively does not have whatever is referenced in another table". Using anything other than NULL for that means you can't use proper foreign keys.
Pxtl 4y ago
> You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string. This is what kills me. This seems like an obvious case where multiple flavors of NULL would make perfect sense. For example Ticket.CancellationDate isn't "unknown", it's this ticket is not cancelled. But SQL standard doesn't have a way to express "empty value" as distinct from "unknown value", so we're stuck working around the strange implications of using this square peg to fill a round hole. Oracle's bizarre "empty VARCHAR2s are NULL" is strange but is consistent with every other datatype that offers no way to say "empty" but "NULL". 0 is not the same as NULL int and is not the same as empty int. Mindate is not the same as NULL date and is not the same as empty date.
- knorker 4y ago
  Well, for the string case empty string is… the empty string. Storing the exit code and stdout of a program run Oracle allows the state "it's not finished yet" to have exit code representation of NULL, but you cannot say the same for string. "Unknown" is not a perfect map to NULL. "I have no answer for you" is better. Maybe NULL values could use an annotation. But I think that would make things worse. Like let's say you have some query that joins addresses with coordinates. You know that semantically any address without a coordinate has an "unknown" coordinate, not that it authoritatively doesn't have coordinates. But not only do you need to change the data model then, but the query language too, to be able to express the difference where absense means one or the other. Now absense is NULL, and leaves it up to the application to interpret. SELECT a.address, b.coord FROM a LEFT OUTER JOIN (SELECT address,coord FROM coords WHERE is_test=FALSE) AS b ON a.address=b.address; Or maybe some coordinates are missing because their locations no longer exist. The house has been demolished, so it's not "unknown" where the house was, it's just that it no longer has a location. And how would you join that data. I don't think I've encountered a problem in practice with SQL in differentiating empty from unknown. It's out of scope of what SQL tries to solve, and that's fine.

SnowHill9902 4y ago

NULL means that the value is unknown, not that the data is unknown. You indeed have no data but its value may exist. You just don’t know it yet. NULL is very powerful when you understand that.

ryanbrunner 4y ago
That seems like a fairly big assumption on how I'm modelling the data though. Particularly in the LEFT JOIN example, I'm not sure if I agree with the idea of "a row not being present means that it's unknown whether it exists in the real world" depending on the underlying content being modelled. For a string column, you do have blanks vs. NULLs, so it's fairly easy to distinguish between "John definitely doesn't have a middle name" vs "It's unknown whether John has a middle name", but that doesn't hold true with most other datatypes.
- SnowHill9902 4y ago
  The string ‘’ is as arbitrarily blank as 0 is a blank integer.
  - wongarsu 4y ago
    There are two "blank" integers: 0 is the identity element under addition, 1 is the identity element under multiplication. Or worded differently 0 is a blank addend, 1 is a blank multiplier. ‘’ would be the identity element for string concatenation, or any other string manipulation method I can think of, so it has a stronger claim on being blank than a 0 has. Unless your string is later parsed as something else. A blank JSON value would look differently from a blank phone number.
    - SnowHill9902 4y ago
      Sure, but it’s arbitrary from a data structure standpoint and sloppy design.
      - 4y ago