3 ms·

That is precisely the point in any data oriented application (like a database) a boolean should actually have at least 3 possible value: - True - False - Nul

That is precisely the point in any data oriented application (like a database) a boolean should actually have at least 3 possible value: - True - False - Null/Nil/unset/error Depending on how the application handle the third case it can be more than 3 return values. timestamp require ad-hoc and potentially breakable workaround to emulate this behavior.

jchw 4y ago
I'll have to disagree. I don't think I'd want that in most cases. It's unclear what general problem it solves. If the database row is actually corrupted, I want the query to fail violently, and the database to fail all operations around it to avoid further corruption. If I really need to handle it, I'll handle it in my app layer. If it's not, I don't want it to be set to some invalid state. I don't want every piece of code to need to deal with this invalid state that shouldn't be allowed to begin with. I'd rather gate it at the door and stop it from getting in there in the first place. If you use a nullable timestamp as a boolean, this is fine; there's only two states w.r.t. set or not: NULL or not NULL, from the SQL perspective. How this is encoded in your language and tools of choice depends. If there's some database value not encodable to your language's native types, the db connector will probably decide what to do: truncate data, fail the whole query, use a type with additional entropy (like Go's NullString, for example.) In no way do I want there to be an error state for that field alone. If I did, though, for example because I need to handle partial data as an application concern, I'd want union typing. In that case you could have Error/Unset/Timestamp just fine. I don't believe SQL has this sort of union type natively, but it can be represented in many programming languages intuitively (like unsafely as a union in C, safely as an enum in Rust, safely as a discriminated union in TypeScript, and mostly safely in Go abusing interfaces.) However, that is only something I want to do if my application specifically needs it. Otherwise? Data that goes into the database must meet the constraints first.
- Twisell 4y ago
  Still after all this explaining you agree there is only two state instead of three. Usually it's used with the following logic for database oriented workflow. True = true False = false Null = unknow (This which might be a real-world case that you can model using null) Of course if there is an error the engine will thrown an exception Which is a whole other kind of signal.
  - jchw 4y ago
    What I don't get is, why would I want there to be a third state in this boolean example? You're saying it could be useful. That's true, it could. However, we're talking about something that today is already just a boolean, such as "is_published." Even if you can envision a reason why you might want a third unknown state for "is_published", if you write a bunch of code that treats it as a boolean, without knowing what the null state might mean, you are not solving a problem. You're just modelling your data slightly less precisely. Saving a date is saving known, useful state, even if you don't need it yet. Using a nullable type for a field that can't yet be null is just adding an additional unknown state for no reason. When using Go and sqlc, what would happen is I would use a NOT NULL field for the boolean, because I'm modelling a boolean, not a tri-state. If I was modelling something where I had three states I wanted to handle, I would use an enum instead. But even if I wanted to use NULL instead of an enum that explicitly specifies the meaning of the third state, I'd rather start as NOT NULL. Why? Because then, if I ever want to remove the constraint, I can. And when I do, sqlc will helpfully change the type of the field from a raw boolean to a nullable boolean wrapper. And doing this will break the existing code. This is good. Why? Because in every place where I assumed there were only two possible states, I can now check and see what I'd want to do in my new third case. This is better than starting with nullability, because if I don't know what NULL means or why I'd have it, it's really difficult to know what my code should do when it encounters it. You could always throw errors, but if you do that you have to ensure you clean all of that up before you start actually using NULL. This is fundamentally about data modelling though. Talking in this abstract sense is not useful. What I feel you're failing to make an adequate case for is why a field like the one described in the linked article needs a state other than "set" or "unset". Just putting a nullable timestamp is a nearly free upgrade from putting a non nullable boolean, which is what I would've done anyways. If I want to add more states, I want explicit data model changes, that result in explicit test and type checking errors, that highlight wherever I need to update my code's state management to account for new states. Additional note: particular to SQL, removing not NULL constraints is basically free from the database end as far as I can tell. In addition, NULL has specific behaviors, especially w.r.t. JOINs, so I would NOT want to use NULL to model a tri-state.
    - 4y ago

jchw 4y ago

I'll have to disagree. I don't think I'd want that in most cases. It's unclear what general problem it solves. If the database row is actually corrupted, I want the query to fail violently, and the database to fail all operations around it to avoid further corruption. If I really need to handle it, I'll handle it in my app layer. If it's not, I don't want it to be set to some invalid state. I don't want every piece of code to need to deal with this invalid state that shouldn't be allowed to begin with. I'd rather gate it at the door and stop it from getting in there in the first place. If you use a nullable timestamp as a boolean, this is fine; there's only two states w.r.t. set or not: NULL or not NULL, from the SQL perspective. How this is encoded in your language and tools of choice depends. If there's some database value not encodable to your language's native types, the db connector will probably decide what to do: truncate data, fail the whole query, use a type with additional entropy (like Go's NullString, for example.) In no way do I want there to be an error state for that field alone. If I did, though, for example because I need to handle partial data as an application concern, I'd want union typing. In that case you could have Error/Unset/Timestamp just fine. I don't believe SQL has this sort of union type natively, but it can be represented in many programming languages intuitively (like unsafely as a union in C, safely as an enum in Rust, safely as a discriminated union in TypeScript, and mostly safely in Go abusing interfaces.) However, that is only something I want to do if my application specifically needs it. Otherwise? Data that goes into the database must meet the constraints first.

Twisell 4y ago
Still after all this explaining you agree there is only two state instead of three. Usually it's used with the following logic for database oriented workflow. True = true False = false Null = unknow (This which might be a real-world case that you can model using null) Of course if there is an error the engine will thrown an exception Which is a whole other kind of signal.
- jchw 4y ago
  What I don't get is, why would I want there to be a third state in this boolean example? You're saying it could be useful. That's true, it could. However, we're talking about something that today is already just a boolean, such as "is_published." Even if you can envision a reason why you might want a third unknown state for "is_published", if you write a bunch of code that treats it as a boolean, without knowing what the null state might mean, you are not solving a problem. You're just modelling your data slightly less precisely. Saving a date is saving known, useful state, even if you don't need it yet. Using a nullable type for a field that can't yet be null is just adding an additional unknown state for no reason. When using Go and sqlc, what would happen is I would use a NOT NULL field for the boolean, because I'm modelling a boolean, not a tri-state. If I was modelling something where I had three states I wanted to handle, I would use an enum instead. But even if I wanted to use NULL instead of an enum that explicitly specifies the meaning of the third state, I'd rather start as NOT NULL. Why? Because then, if I ever want to remove the constraint, I can. And when I do, sqlc will helpfully change the type of the field from a raw boolean to a nullable boolean wrapper. And doing this will break the existing code. This is good. Why? Because in every place where I assumed there were only two possible states, I can now check and see what I'd want to do in my new third case. This is better than starting with nullability, because if I don't know what NULL means or why I'd have it, it's really difficult to know what my code should do when it encounters it. You could always throw errors, but if you do that you have to ensure you clean all of that up before you start actually using NULL. This is fundamentally about data modelling though. Talking in this abstract sense is not useful. What I feel you're failing to make an adequate case for is why a field like the one described in the linked article needs a state other than "set" or "unset". Just putting a nullable timestamp is a nearly free upgrade from putting a non nullable boolean, which is what I would've done anyways. If I want to add more states, I want explicit data model changes, that result in explicit test and type checking errors, that highlight wherever I need to update my code's state management to account for new states. Additional note: particular to SQL, removing not NULL constraints is basically free from the database end as far as I can tell. In addition, NULL has specific behaviors, especially w.r.t. JOINs, so I would NOT want to use NULL to model a tri-state.
  - 4y ago