`select` behaves unexpectedly when underlying data is undefined

[danielberndt]

Hi there, I'm just upgrading a hobby project from PouchDb to TinyBase and I'm really enjoying the process so far! The code is getting so much simpler!

There has been one unexpected surprise though. I'm working on a shopping list app and items can have an optional categoryId.

Here's my query for finding the categoryId for all incomplete icons:

queries.setQueryDefinition("incompleteItems", "items", ({select, where}) => {
  select("categoryId");
  where("done", false);
});

now queries.getResultTable("incompleteItems") will only return entries for which a categoryId is present, omitting all items with an undefined categoryId. This was rather unexpected.

I've found two workarounds though:

1. Use a computed value and use an empty string for empty values

queries.setQueryDefinition("incompleteItems", "items", ({select, where}) => {
  select((getCell) => getCell("categoryId") ?? "").as("categoryId");
  where("done", false);
});

2. Select a non-optional field as well

queries.setQueryDefinition("incompleteItems", "items", ({select, where}) => {
  select("categoryId");
  select("createdAt")
  where("done", false);
});

None of which feel very elegant.

My question is whether omitting rows with empty selected cells is the desired behaviour. If so, it might be a worth to call it out in the docs.

In any case, this has been a fairly minor issue on my journey, and I really do appreciate what you have accomplished with this project! 😊

[jamesgpearce]

Interesting. Yes, TinyBase generally uses 'undefined' and the concept of 'missing' interchangeably, and in the case of queries, if all the cells are empty/undefined, that is the equivalent of a row not existing. Definitely when I read the start of the issue, my first thought was to adopt the calculated approach you took.

In coming releases I think we will have to tackle the requirement of having nulls in raw tables, and that will perhaps open up the opportunity to have nulls returned in queries (when no value was present). Until then I'm a little loathe to change the semantics too radically.

I will think about whether an optional flag on the select or something like that could be used, but no immediate promises!

[danielberndt]

Thanks for your input! No hurry from my side as there exist simple workarounds. I was mostly curious because I couldn't find this behaviour documented anywhere.