What are our design goals around distinguishing between NULL and empty string

[seancolsen]

Broadly speaking, in terms of our overall product goals, if a column has a string type, do we plan to distinguish between NULL values and empty strings?

These points seem to imply that we're presenting null and empty string as the the same, conceptually:

• The Working with Columns spec says:

  To simplify the way we handle the filtering of columns based on the content of a field, we have nested the NULL condition under the ‘Empty’ definition, which also includes empty strings.

• The prototype specifies an "Allow Empty" setting. While I appreciate the simplicity of that language, I can see how it leaves little room for distinguishing between null and empty string, conceptually.

However, I would assume we'd want to expose the users to the distinction between null and empty string, so that's why I'm asking -- because I found the above surprising. I don't feel too strongly about this. Not trying to debate it here. Just seeking clarity.

@kgodey @ghislaineguerin @pavish @mathemancer (tagged because I'm not sure if you get notified of new discussion topics -- side note this discussion feature is new to me, but I really like it 🙂 )

[mathemancer]

Apparently there's no way (that I can see) to just comment or reply. Just "answer". But, consider this more of a comment.

I didn't read closely enough when looking through the final spec before; oops. I don't think treating empty strings and nulls the same is a good idea. They behave differently on the DB:

'a' = null -> null
'a' = '' -> false   -- empty string
'a' <> null -> null
'a' <> '' -> true -- empty string

Note that Uniqueness constraints involve disallowing equalities. Thus, because null isn't equal (or unequal) to anything, columns with uniqueness constraints which allow empty values will behave differently based on whether those empty values are empty strings or nulls. I suspect this would be confusing for many users.

[seancolsen]

I assumed we already had an agreed-upon strategy here, but maybe we don't. Sorry if I chose a sub-optimal thread format.

[seancolsen]

Just in can we don't already have a decision, I'll offer my opinion on what would make the best design goal:

We should treat NULL and empty string as distinct values and concepts in all cases.

Adopting that design goal might warrant making some changes to our existing designs, but personally I think it would behoove our product and line up well with our principle of "not hiding database concepts". If the above goal were true, I would suggest changing the "Allow Empty" UI language to "Allow NULL". But that's just an example. I see those small decisions as beyond the scope of this thread. For this thread, I'm seeking clarity/agreement on the overarching goal/strategy that we can then use to guide those smaller decisions going forward.

[mathemancer]

We should treat NULL and empty string as distinct values and concepts in all cases.

I agree. I think we'll introduce more confusion than we save otherwise.

[pavish]

I agree with treating them as different values. NULL and empty string are not the same.

We do currently show them differently on the UI. Null values are shown as null, with a different color (which upon edit turns into an empty text field), and empty strings are shown as empty cells.

I would suggest changing the "Allow Empty" UI language to "Allow NULL"

I agree that we need to rename this to Allow Null.

[kgodey]

I agree that we should treat empty strings vs. null differently (and as @pavish pointed out, we already do when showing values in columns). I'm not sure about the UI text being "Allow NULL", we're trying to balance showing DB concepts with being friendly to people that aren't familiar with them I would prefer a less technical word that's routinely used, like perhaps "blank".

For another example of how we're simplifying database concepts for non-technical users, see the Mathesar data type mapping.

Also see: Word suggestion - "empty" vs "blank" on the UX Stack Exchange.

[seancolsen]

Although we still haven't heard from @ghislaineguerin, it seems like we are mostly in agreement on the high-level goal of treating NULL and empty string as different values and presenting them as different concepts to the user. For the purpose of moving forward with a decision, I'm going to mark the answer as accepted. For the example of "Allow Empty" vs "Allow Blank" vs "Allow NULL", let's continue that discussion within a new (open-ended) thread here: What's the best terminology to use for NULL?

[ghislaineguerin]

@mathemancer @seancolsen @pavish
I agree that we shouldn't treat both the same. When we were working on filters, there was a scenario where we needed to show all rows with an empty value, @kgodey suggested we treat both the same but I can't recall why exactly. We probably need to include a filter option for NULL vs Empty and that should solve it.