find.le broken with negative sorts

[combinatorist]

Describe the current behavior

Currently, if I sort the values 1,2, and 3 in an ID column with:

Values.lookupRecords(sort_by="-number").find.lt($number)

Then, the row for 2 will return 3 as "less than" it, which is plainly false.

Steps to reproduce

1. open my demo document: https://tim-eccleston.getgrist.com/cqnivonzeYqi/Sort-Bug-Demo?utm_id=share-doc
2. examine the third column: descend-less-than

Describe the expected behavior

In this context, I would expect lt on a negative (descending) sort to still return some value that is in fact less than 2 (i.e. 1).

---

It seems that the comparison methods have different meanings:

• lt -> "previous"
• le, ge -> "current"
• gt -> "next"

If this is intentional, then it should be a lot clearer in the documentation and ideally the names. Also, it's apparent that there isn't really a difference between le and ge (unless these can be used to return multiple results somehow, maybe without a find).

It is also very strange that the sort is based on a string (is that sqlite?). The way the negative sign is interpreted seems to be the source of the nonsensical result. Could we just do away with the string altogether and use a value (or tuple of values)? That would also fix another bug.

Where have you encountered this bug?

• On docs.getgrist.com
• On a self-hosted instance

Instance information (when self-hosting only)

No response

[dsagal]

It is indeed intentional that lt & co mean "previous"/"next"/"previous-or-equal"/"next-or-equal". Strict meaning of "less" and such becomes impossible when ordering is complex (e.g. order_by=("Name", "-Date")). If you have suggestions to improve documentation, we are interested!

The difference between le and ge should be visible when looking up a value that's NOT in the table. E.g. if you have values 10, 20, 30, and do .find.le(20) or .find.ge(20).

As for any other awkward corners, please check them using order_by, which replaces the deprecated sort_by, and in fact has improved behavior in some corner cases.

[combinatorist]

Ok, I feel convinced by the design (Yay!), but not the names/documentation.

I personally feel torn between the concise abbreviations these well-known comparators provide and the fact that their meaning is stretched here. It would be more accurate to use the terms you quoted above, but much less concise, so I suppose my recommendation is to improve documentation. I'll make a quick PR based on your helpful comments above and link it here when it's done.

[combinatorist]

I'll just note that the terms NEXT and PREVIOUS are actually used in the cumulative XL-like functions. :/

[combinatorist]

In making my proposed changes to the docs, I noticed it gets even more complicated in the le/ge cases because you could have multiple equals matches. So, I'm guessing if there's at least one match, it takes the first one in either case, which could lead to indeterminate results and strange nuances - for example, that both le and ge return the same value and ignore the other matched based on implicitly ordering (tie-breaking) by RowID.

[combinatorist]

Here's my docs PR FWIW.

[combinatorist]

I'm only leaving this open in case the authors want to create aliases for these methods:

• lt -> strict_before
• le -> on_or_before
• gt -> strict_after
• ge -> on_or_after
• eq -> on (this one doesn't need an alias)

Or some other analogous scheme (maybe replacing on with eq? etc). But, barring some renaming, this PR could close and hopefully the docs will get more clear.