Null string values - decision needed

[kpodp0ra]

Here is what I discovered

1. Backend allows you to put empty strings as a text value
2. Frontend changes them to null before sending API request
3. Null values and empty strings are looking the same in UI
   
4. There is a bug in aggregation:
   

   teable/apps/nestjs-backend/src/features/aggregation/aggregation.service.ts

   Lines 199 to 200 in 8b3df68

   	const flagString = `${currentGroupFieldId}_${groupByValueString}`;
   	const groupId = String(string2Hash(flagString));

   
   Aggregation gives the empty string and the null string the same hash (see screenshot above where count is 4, not 2)
5. isEmpty and isNotEmpty checks only for null values
   Resolution: fix: better match empty text fields #1181

Here is what ChatGPT thinks:

It's crucial to allow users to distinguish between empty strings and NULL values in databases because they represent different states with real-life implications. Here are key use cases where this distinction matters:

• Contact Information: Empty strings mean the user explicitly chose not to provide data, while NULL signifies the data hasn't been addressed yet.
• Survey Responses: Empty strings indicate a deliberate choice to leave a question blank, while NULL reflects that the question wasn't shown or interacted with.
• E-Commerce: Sellers can indicate no product description is needed (empty string), while NULL flags pending updates.
• Application Settings: Empty strings express "no preference," whereas NULL highlights unset configurations.
• Medical Records: Empty strings confirm no allergies, while NULL signifies unknown allergy status.

My opinion

I know that implementing such a distinction may require some planning and work, but for me, it would be really useful if the null values would be shown as a dark dot or something like DataGrip has. This would allow me to distinct which values are empty on purpose and which are not yet filled.

Remember that you can hide fields in views, and these fields stay hidden in creation-modal. If one people in the company create records and others edit them, it is good to distinguish which fields have been populated.

Also, we have NULL and 0 values in number fields, why we don't implement this mechanic in strings?

I need feedback and decision what path we should go

If we want to keep null values and empty fields unambiguous, I would consider unifying the backend and frontend by changing the empty field to null on the server side.

But if we want to keep null values and empty fields separate, as LLM suggests, we should discuss if we want to and how to distinguish them visually in UI. JetBrains DataGrip does it like this:

We would also need some context-action to set NULL values:

Also, we would need NULL-button here:

And then there is the side issue of how we do with grouping. Should the null and empty string be treated by the aggregation as the same, or should we keep them in separate groups (as it is currently).

[tea-artist]

I currently prefer not to save empty strings at the Teable application and API level, and to convert all empty strings to null before saving, In Excel, empty strings are also treated as null, which seems to be more intuitive for non-tech people. (by the way display <null> will also confuse non-tech people

[alexfacciorusso]

I see both sides, as in I also don't like saving an empty value, and instead maybe prefer putting a checkbox (a boolean) setting if the question got answered. On the other hand, having some data manipulation that might be unwanted and not immediately exposed to the user/developer is also weird.

What I suggest is maybe adding an option in the field like "Convert empty strings to NULL values", which is disabled by default, and the user can enable it and get the behaviour that's currently in the app.

[tea-artist]

@alexfacciorusso I think this is a good idea. But, I think the default state should be enabled for "Convert empty strings to NULL values". Users should turn it off only when they clearly need an empty string.

Similarly, if this option is turned off, display a <null> in view when the cell is null to distinguish it.

@kpodp0ra What do you think?

[tea-artist]

For the grouping situation, distinguishing between null and empty strings should be in line with expectations because we can already use <null> to visually distinguish.

[alexfacciorusso]

@alexfacciorusso I think this is a good idea. But, I think the default state should be enabled for "Convert empty strings to NULL values". Users should turn it off only when they clearly need an empty string.

Similarly, if this option is turned off, display a <null> in view when the cell is null to distinguish it.

Sounds fair to me, yes if the field is disabled then the user can clearly see <null> in the cell

[tea-artist]

TablePlus uses "empty" to represent an empty string. I think this is also a good idea. We can render a light-colored "empty" mark in the cells of empty strings in the table to distinguish empty strings. In this way, completely empty cells can be used to represent null.

[kpodp0ra]

@tea-artist what do you think about optional, non default three checkbox states?

[tea-artist]

Agree, at present, our processing in the application is all set to true or null. However, considering the need to access external data sources in the future, we do have to handle all cases.

@tea-artist what do you think about optional, non default three checkbox states?