4 ms·
I'm going to have to respectfully disagree on this one, just because of unintended behavior and complexity. Whether it's in a programming language or in a data
by crazygringo 4y ago
I'm going to have to respectfully disagree on this one, just because of unintended behavior and complexity.
Whether it's in a programming language or in a database... what is false? Is it false or null or 1970-01-01 00:00:00 UTC? What if different parts of the program/table use different versions of false? What happens when I combine two truthy values with a boolean operator -- for all combinations of values, what is the resulting type and is it the value even correct? (E.g. in SQL, NULL XOR 1672254005 is NULL, which is flat-out wrong if you're using NULL in place of FALSE, and it's easy to think of further examples.)
There's so much research I'd have to do to determine that this would always be bug-free for a particular language/platform, and then I'd need to remember which languages/platforms it is safe for and which ones aren't.
- echelon 4y agoFor nullable timestamps, the timestamp itself is opaque. Having a value is indication that the thing happened. The time reading is orthogonal to that question. If your language has a tough or flexible time with falsy, I found your problem. You shouldn't even be in that headspace, and languages that put you there introduce so much mental overhead that they're not worth it. You shouldn't need to do research here. This should be a one and done solution from your toolbox that you can pull out at a moment's notice: "How do I handle record soft deletion and store some state around when it happened?" - zero effort, tried and true recipe borne of experience. The database and data stores drive your product. They're the heart and soul.
- crazygringo 4y agoSo the problem is with SQL, because NULL doesn't act the same as false, and boolean operators may give incorrect results if you expect them to? I'm not sure how to respond if you think people shouldn't be in the headspace of SQL.
- echelon 4y agoIn SQL you won't typically coerce null to false, though there are mechanisms to accomplish this (which can be useful for analytics, ETLs, etc). Presence is first class and is probably what you want in your business logic queries. If you let the SQL query predicates handle which records to select, I'm not sure I see the problem you originally outlined. Why do you care what value the timestamp has when the question is whether or not a record experienced an event.
- jchw 4y agoNo, you have to do this anyways. Assuming you're using a SQL database, a boolean field can be NULL too, unless you explicitly mark it non-nullable. If you are worried about obscure edge cases, use languages and tools with less of them. For example, it is VERY easy to reason about the date field being set or not if your language of choice represents it with an optional type you have to unwrap in some fashion. But it's not really that bad in the worst case. Failing docs or the ability to read your db connector's source code enough to ascertain, you can always just test. Both in the sense of, try it and see what happens, and also, writing automated tests that ensure you're getting the expected behavior. If your line of logic is that hope is not a strategy, I agree. However, there are alternatives to hope that don't involve vacating best practices out of fear of hitting edge cases, and you probably want those automated tests anyways.
- Twisell 4y agoThat 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 agoI'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.
- mixmastamyk 4y ago> false or null Not possible with a timestamp field. > 00:00:00 UTC I remember a bug around this in Python regarding times (only) a decade or two ago, it was fixed a long time ago. I assume this is not an issue for modern stacks, but I would double check under say PHP which has a known history. Not normally an issue I'd say. > What happens when I combine two truthy values with a boolean operator Type or Runtime error. Who is adding booleans and dates together? Such egregious mistakes don't take long to surface and are impossible if planned against beforehand. I don't think these are things to be worried about (if you're paying attention even a tiny bit) to be honest.