3 ms·

Re: I think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value. No, that would muddy things in my opinion, l

by tabtab 7y ago

Re: I think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value. No, that would muddy things in my opinion, like it did to JavaScript. Instead, have more operations/functions for dealing with them in a more "normal" way, so that we can say "WHERE x <> 5" and get results one expects. I'm not sure the syntax, and my drafts would take a lot of time to explain. To give a taste, maybe have something like "WHERE ~x <> 5" in which the tilde converts x's value to the type's default, such as a blank in the case of strings. If the different reasons for "emptiness" matter, then usually it suggests the need for a "status" column of some kind so that queries can be done on the reasons. I'd need to study domain specifics to recommend something specific.

magicalhippo 7y ago
But that would mean you need to be aware that the column can have this property, no? Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction. In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator. In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.
- tabtab 7y ago
  Re: I'd have to remember to go over my query to add the ~ operator. I'd almost always use it no matter what. In fact if starting SQL over via a DeLorean, I'd reverse it to require "~" to make it null-sensitive. It's analogous to case-sensitive comparing. The vast majority of the time you don't want case-sensitive comparisons such that case-insensitive should be the DEFAULT, and you only add extra specifiers/functions if and when you need to do case-based comparing. If not, then you either end up with SQL full of "toUpperCase(x)", or problems keep popping up if somebody typed stuff wrong or the UI coders messed up. Similarly, if your SQL is full of NVL() or DENULL() or whatnot, it means the language was poorly factored for your domain, or even most domains. It flunked D.R.Y. in design, such as comparisons having the wrong default behavior.
  - magicalhippo 7y ago
    Yeah that'd be interesting.

by tabtab 7y ago

magicalhippo 7y ago

But that would mean you need to be aware that the column can have this property, no? Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction. In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator. In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.

tabtab 7y ago
Re: I'd have to remember to go over my query to add the ~ operator. I'd almost always use it no matter what. In fact if starting SQL over via a DeLorean, I'd reverse it to require "~" to make it null-sensitive. It's analogous to case-sensitive comparing. The vast majority of the time you don't want case-sensitive comparisons such that case-insensitive should be the DEFAULT, and you only add extra specifiers/functions if and when you need to do case-based comparing. If not, then you either end up with SQL full of "toUpperCase(x)", or problems keep popping up if somebody typed stuff wrong or the UI coders messed up. Similarly, if your SQL is full of NVL() or DENULL() or whatnot, it means the language was poorly factored for your domain, or even most domains. It flunked D.R.Y. in design, such as comparisons having the wrong default behavior.
- magicalhippo 7y ago
  Yeah that'd be interesting.