8 ms·

There's a lot of real-world input situations in which I want NULL to indicate that a record field hasn't yet been collected, versus a record in which the answer

by danso 7y ago

There's a lot of real-world input situations in which I want NULL to indicate that a record field hasn't yet been collected, versus a record in which the answer to that field was "" – i.e. left blank by the respondent/data source. Sure I could have the front-facing data app auto-convert blank values to "N/A" or "(blank)", but that's unnecessary complexity.

SigmundA 7y ago
Empty string isn't enough for this situation since it doesn't work for data types other than string, I actually prefer null vs undefined in javascript / json here, xml also has the concept of null vs undefined. Emtpy string vs null is not that useful if you have null and undefined.
- giornogiovanna 7y ago
  Just to be clear, undefined is not valid in JSON. The only types in JSON are nulls, strings, numbers, arrays, booleans and objects.
  - cyphar 7y ago
    "undefined" in this context means that the JSON object has had the relevant key omitted rather than explicitly set to null. This is valid JSON (it just so happens that JavaScript handles this scenario by returning "undefined" as with all undefined variables).
    - giornogiovanna 7y ago
      Ah, right, my bad. In my defense, JavaScript treats a value of `undefined` differently from an unset field / variable (e.g. when testing `object.hasOwnProperty('field')`).
      - tabtab 7y ago
        JavaScript has too many "non-visible" value types in my opinion. This makes for both messy code and messy debugging.
    - SigmundA 7y ago
      Yes omitted value vs provided with null which is easy to work with in JS because of null and undefined and them being falsey. Working with xml or json in more typed language like C# is less fun because there is not distinction in the language instead you end up using dom like checks to see if the key was provided or was set to null. .Net code generator use to add an extra property called [property name]Specified for xml serialization so you could do null values or not send the node. Definitely prefer more direct language support for no value provided vs a null value.
- vbezhenar 7y ago
  Undefined is very underappreciated invention. I wish I had undefined in Java, throwing exception on read. That would make a lot of software much more reliable. I'm going to write my own AutoValue library just to generate java beans which will allow uninitialized values. PS undefined in JavaScript is not that useful IMO. But it could be useful with more strict language.
  - fnord123 7y ago
    Optional was added in java 8.
tabtab 7y ago
I've never needed such in 3 decades of systems design. I'd like to hear the details. An explicit flag or time-stamp should be used to indicate when or if a record as been updated. To be frank, heavily reliance on Null strings usually means somebody is doing something wrong or awkward in my opinion. Null strings cause 10 problems for every 1 they solve. I stand by that and will and have defended it for hours in debates. Bring it on! (Granted, most RDBMS don't offer enough tools to easily do it correctly.)
- SigmundA 7y ago
  Did the user leave the field blank in the form or did they never even see it (multi step processes etc.) Did you want to update the field to null or not update it at all is another one.
  - tabtab 7y ago
    What exactly does "not see" mean? Some use tab, others use the mouse. That doesn't tell us much and there are better ways to track user hand/mouse movements if you need such telemetry. UI api's often handle nulls/blanks different such that you don't want to over-rely on how one of them does it. Multi-screen tasks should track when each sub-screen is finished, not just the final transaction. I'd like to see a more specific use-case. Re: Did you want to update the field to null or not update it at all is another one. Who, the user or DBA? And why? Users don't know a Null from a horse; you shouldn't make them think about that, that's an internal guts thing.
    - SigmundA 7y ago
      Was the user prompted to enter the value but left it blank or where they never prompted. Yes you could always have more data, but this extra two value distinction is common and practical in my experience. On updating its related to prompting typically, again communicating user intent, did the user update a field from having a value to not having a value (they purposely blanked it out) or they didn't touch the field and maybe it wasn't even shown so don't modify it. Basically am I going to generate a update statement with set field = null or no set at all for that field. This is trivial in json to send through application due to null vs undefined.
juped 7y ago
Yeah. That's what null is for (more or less).
talaketu 7y ago
I'm sceptical. Do you have examples? Take the middle name example: "What was U.S President Theodore Roosevelt’s middle name?" when you know he didn't have a middle name. Are you suggesting that a blank is the correct choice here? I don't think it's accurate to say they have a blank middle name. I think it's better to say they don't have a middle name.
- kbenson 7y ago
  That's entirely a schema/data policy question. Null could indicate no name, or it just as easily could indicate tha data was not collected, and an empty string would indicate it was collected but is empty. Both are entirely valid methods of encoding the data, and require knowing how the data is intended to be collected, entered and used to determine the best choice.
  - zadokshi 7y ago
    No, what you suggest is counter intuitive and should never pass a code review.
    - kbenson 7y ago
      > No, what you suggest is counter intuitive and should never pass a code review. You're assertion is that it's counter-intuitive to use the database's ability to encode a lack of information to denote a lack of knowledge about the information, and should only be used to denote the information does not universally exist? And additionally that this is so well accepted that to do otherwise would not be accepted by any peers? If so, that's a fairly bold assertion to make when you haven't provided any any evidence or reasoning to back it up.
  - talaketu 7y ago
    Should there be an empty date, as a corollary to empty string, for representing dates that are "collected but empty" eg date of death?
    - 7y ago
sammorrowdrums 7y ago
I've been really enjoying using Rust because it has no NULL. You have to instead use the semantics of Some(x) / None()and handle both cases. I'm not sure how you work around the fact that an empty string is Null. The problem with Null is that it can be either a value or show the lack of presence, and in JS because both undefined and null are values you can't depend on them being used for their semantics either (you can try to enforce their use but it that's no guarantee). SQL suffers this exact same problem. I wonder what SQL would look like without Null. Select id, Option (key) From table Insert... Some(5), None::Int From this perspective it still seems that empty string should not be None, but I think you're right that many people do use a code like "N/A", but you can just add a supplemental Boolean field which is cleaner for set / not set. Interesting
- sammorrowdrums 7y ago
  This still doesn't fix the user input Null, versus this value is not set. I think in that case you'd want a nullable boolean companion field that can then express set, actively not set and passively not set (when that matters) but it's a bit ugly.
  - deleted 7y ago
    [deleted]
  - claudiawerner 7y ago
    Value isn't set? Use Nothing. Data has been collected and the user didn't input anything? Use Just "". If you really wanted to, in Haskell you could do something like: data AdvancedMaybe a = Just a | Nothing | PassiveNothing
    - sammorrowdrums 7y ago
      Interesting example. I certainly think the just use empty "" is fine and the coercion to NULL is not great. I think Allowing "empty" values, and not allowing Null would be better. If SQL had Option (Maybe) branch matching it would be an interesting replacement, and then coalesce would not be needed. SELECT id, MATCH k WITH ( SOME(k) THEN k, NONE THEN default, END) AS something FROM ... I can see it's terse, and it is just a wrapper around data that would be stored like NULL but it kind of makes for interesting semantics. Just spitballing for fun on what it might look like.

by danso 7y ago

SigmundA 7y ago

Empty string isn't enough for this situation since it doesn't work for data types other than string, I actually prefer null vs undefined in javascript / json here, xml also has the concept of null vs undefined. Emtpy string vs null is not that useful if you have null and undefined.

giornogiovanna 7y ago
Just to be clear, undefined is not valid in JSON. The only types in JSON are nulls, strings, numbers, arrays, booleans and objects.
- cyphar 7y ago
  "undefined" in this context means that the JSON object has had the relevant key omitted rather than explicitly set to null. This is valid JSON (it just so happens that JavaScript handles this scenario by returning "undefined" as with all undefined variables).
  - giornogiovanna 7y ago
    Ah, right, my bad. In my defense, JavaScript treats a value of `undefined` differently from an unset field / variable (e.g. when testing `object.hasOwnProperty('field')`).
    - tabtab 7y ago
      JavaScript has too many "non-visible" value types in my opinion. This makes for both messy code and messy debugging.
  - SigmundA 7y ago
    Yes omitted value vs provided with null which is easy to work with in JS because of null and undefined and them being falsey. Working with xml or json in more typed language like C# is less fun because there is not distinction in the language instead you end up using dom like checks to see if the key was provided or was set to null. .Net code generator use to add an extra property called [property name]Specified for xml serialization so you could do null values or not send the node. Definitely prefer more direct language support for no value provided vs a null value.
vbezhenar 7y ago
Undefined is very underappreciated invention. I wish I had undefined in Java, throwing exception on read. That would make a lot of software much more reliable. I'm going to write my own AutoValue library just to generate java beans which will allow uninitialized values. PS undefined in JavaScript is not that useful IMO. But it could be useful with more strict language.
- fnord123 7y ago
  Optional was added in java 8.

tabtab 7y ago

I've never needed such in 3 decades of systems design. I'd like to hear the details. An explicit flag or time-stamp should be used to indicate when or if a record as been updated. To be frank, heavily reliance on Null strings usually means somebody is doing something wrong or awkward in my opinion. Null strings cause 10 problems for every 1 they solve. I stand by that and will and have defended it for hours in debates. Bring it on! (Granted, most RDBMS don't offer enough tools to easily do it correctly.)

SigmundA 7y ago
Did the user leave the field blank in the form or did they never even see it (multi step processes etc.) Did you want to update the field to null or not update it at all is another one.
- tabtab 7y ago
  What exactly does "not see" mean? Some use tab, others use the mouse. That doesn't tell us much and there are better ways to track user hand/mouse movements if you need such telemetry. UI api's often handle nulls/blanks different such that you don't want to over-rely on how one of them does it. Multi-screen tasks should track when each sub-screen is finished, not just the final transaction. I'd like to see a more specific use-case. Re: Did you want to update the field to null or not update it at all is another one. Who, the user or DBA? And why? Users don't know a Null from a horse; you shouldn't make them think about that, that's an internal guts thing.
  - SigmundA 7y ago
    Was the user prompted to enter the value but left it blank or where they never prompted. Yes you could always have more data, but this extra two value distinction is common and practical in my experience. On updating its related to prompting typically, again communicating user intent, did the user update a field from having a value to not having a value (they purposely blanked it out) or they didn't touch the field and maybe it wasn't even shown so don't modify it. Basically am I going to generate a update statement with set field = null or no set at all for that field. This is trivial in json to send through application due to null vs undefined.

juped 7y ago

Yeah. That's what null is for (more or less).

talaketu 7y ago

I'm sceptical. Do you have examples? Take the middle name example: "What was U.S President Theodore Roosevelt’s middle name?" when you know he didn't have a middle name. Are you suggesting that a blank is the correct choice here? I don't think it's accurate to say they have a blank middle name. I think it's better to say they don't have a middle name.

kbenson 7y ago
That's entirely a schema/data policy question. Null could indicate no name, or it just as easily could indicate tha data was not collected, and an empty string would indicate it was collected but is empty. Both are entirely valid methods of encoding the data, and require knowing how the data is intended to be collected, entered and used to determine the best choice.
- zadokshi 7y ago
  No, what you suggest is counter intuitive and should never pass a code review.
  - kbenson 7y ago
    > No, what you suggest is counter intuitive and should never pass a code review. You're assertion is that it's counter-intuitive to use the database's ability to encode a lack of information to denote a lack of knowledge about the information, and should only be used to denote the information does not universally exist? And additionally that this is so well accepted that to do otherwise would not be accepted by any peers? If so, that's a fairly bold assertion to make when you haven't provided any any evidence or reasoning to back it up.
- talaketu 7y ago
  Should there be an empty date, as a corollary to empty string, for representing dates that are "collected but empty" eg date of death?
  - 7y ago

sammorrowdrums 7y ago

I've been really enjoying using Rust because it has no NULL. You have to instead use the semantics of Some(x) / None()and handle both cases. I'm not sure how you work around the fact that an empty string is Null. The problem with Null is that it can be either a value or show the lack of presence, and in JS because both undefined and null are values you can't depend on them being used for their semantics either (you can try to enforce their use but it that's no guarantee). SQL suffers this exact same problem. I wonder what SQL would look like without Null. Select id, Option (key) From table Insert... Some(5), None::Int From this perspective it still seems that empty string should not be None, but I think you're right that many people do use a code like "N/A", but you can just add a supplemental Boolean field which is cleaner for set / not set. Interesting

sammorrowdrums 7y ago
This still doesn't fix the user input Null, versus this value is not set. I think in that case you'd want a nullable boolean companion field that can then express set, actively not set and passively not set (when that matters) but it's a bit ugly.
- deleted 7y ago
  [deleted]
- claudiawerner 7y ago
  Value isn't set? Use Nothing. Data has been collected and the user didn't input anything? Use Just "". If you really wanted to, in Haskell you could do something like: data AdvancedMaybe a = Just a | Nothing | PassiveNothing
  - sammorrowdrums 7y ago
    Interesting example. I certainly think the just use empty "" is fine and the coercion to NULL is not great. I think Allowing "empty" values, and not allowing Null would be better. If SQL had Option (Maybe) branch matching it would be an interesting replacement, and then coalesce would not be needed. SELECT id, MATCH k WITH ( SOME(k) THEN k, NONE THEN default, END) AS something FROM ... I can see it's terse, and it is just a wrapper around data that would be stored like NULL but it kind of makes for interesting semantics. Just spitballing for fun on what it might look like.