Expressions: New option for faster and more consistent comparisons

Hey everyone! In the coming days, we will enable the option for expressions in apps to use new behavior for comparison operators (=, <>, <, <=, >, >=). This will be an improvement in sync performance and consistency of results.

The current behavior will be called the Legacy option and the new behavior will be called the Consistent option. The ability to change an app between Legacy and Consistent will be added under Data > Options > Expressions > Blank value comparison mode.

Performance Improvement

We improved the performance for comparison operators when they are evaluated on the AppSheet server, such as in virtual columns. Since virtual columns are recomputed on app sync, this may improve the speed of sync.

Consistent Comparisons

The Legacy comparisons behavior is known to have some issues with accuracy and consistency between expressions evaluated in the server and client (e.g., app formulas in virtual columns vs. ones in physical columns). These issues can cause unexpected behavior in apps, and can take a long time for app creators, support, and devs to track down. Here is an example of one of those issues. The Consistent option resolves this issues with the changes summarized in the table below. The examples use column notation to represent a blank value ("[Blank]"), but it represents any expression that evaluates to a blank value.

Expression example Server (Legacy) Client (Legacy) Server and Client (Consistent)
1 = [Blank] true false false
[Blank] = [Blank]
[Blank] <= [Blank]
[Blank] >= [Blank]
true true true
[Blank] = 0
[BlankDuration] = “000:00:00”
false true false
[BlankDateTime] = “12/31/1969 6:00:00 PM”
[BlankDate] = “12/31/1969”
[BlankTime] = “06:00:00 PM”

(The value on right side is the user TZ offset from UTC unix epoch)
false true false
[Blank] <> 0 true false true
[Blank] < 0
[Blank] > 0
false false false
[Blank] <= 0
[Blank] >= 0
false true false
[Blank] < 1
[Blank] <= 1
[Blank] > -1
[Blank] >= -1
false true true

FAQ

Why is the option opt-in?
Since it changes the behavior for how comparison operators handle blank values, the Consistent option is not strictly backwards-compatible with existing expressions. We’ve made both behaviors available to give app creators time to adapt to the new behavior. In several months, the app editor will begin explicitly encouraging use of the new behavior. Eventually, the old behavior will be removed entirely.

How are new apps or apps created by copying another app affected?
New apps will default to Consistent, but along with all apps, can be switched between Legacy and Consistent in the app editor. Apps created by copying another app will use the same comparison mode as the copied app.

What should I do if using the Consistent option makes my app work differently than I intend?
If your app depended on the original behavior such that switching to Consistent broke your app, switch back to Legacy to restore the original behavior. From there, you can make a copy of the original app as a test version, switch the test app to Consistent, then make the necessary changes to the test app so that you end up with a stable app using the Consistent option.

11 Likes

:partying_face:

Will this affect this at all

?

1 Like

Hi @Marc_Dillon – this does not affect that feature request

2 Likes

I applaud you and AppSheet in recognizing the issues in inconsistent behavior between the client and servers and the need to sync the behavior between the two.

I would like to point out that there still is a flaw that will result in problematic comparisons and while it may be easier with the “Consistent” implementation it can still be difficult to identify, especially in complex expressions.

In the image below, I am highlighting the contradictory behavior. Once the decision is made that [Blank] <> 0, it is contradictory to imply that [Blank] is less than 1 and that [Blank] > -1. This is because these are INTEGER values and the only logical value [Blank] can be is ZERO - if <1 is in fact TRUE.

This also begs the question…what is the result of “[Blank] < 1.0”??


I am not sure why AppSheet is re-inventing the wheel when it comes to the treatment of [Blank], Empty String, Space and Zero. There are well documented implementations. Maybe you are trying to simplify expressions by eliminating the need to test for [Blank] everywhere? The past has proven that trying to avoid that complexity doesn’t work.

IMHO, in would be best to treat [Blank] as a text value that is different than Space. (You may be able to treat [Blank] and Empty String as the same.) This would mean that [Blank] is not a valid operand in comparisons against numbers - just like any other text value such as “A”, “B”, “C”, etc - and would result in an error when used in a numeric comparisons - whether NUMBER type or DECIMAL type.

5 Likes

Thanks for pointing that out @WillowMobileSystems . The comparison against 1 section being at the bottom, I totally glossed over it on my first review.

Blank, or null, values are acceptable values in Number or Decimal columns though. What do you mean by “result in an error”?

I think any blank value being compared as equal, less than, or greater than, any number/decimal, should return FALSE. And conversely, being compared as not equal to any number/decimal should return TRUE.

To clarify my position, I agree with the entire table in the OP, except for this portion:

image

@natalie , can you comment on the reason why it was decided like this?

3 Likes

I stand corrected and agree with you, any numeric comparison with [Blank] should return FALSE.

Good point and I didn’t think my proposed implementation through well enough for a no-code platform - I had a flashback to my days as a c-sharp developer and was thinking of runtime instances.

In our use case here, we don’t know what the runtime value will be and [Blank] is a valid column value or returned value from a sub-expression. So we DO need to accept it as a valid value

HOWEVER, this WILL lead to confusion and error detection problems. We are human and there will be scenarios where we THINK the value simply cannot be [Blank] when in fact it actually is. Or we simply do not think of this as a use case. We’ll bang our heads on our keyboards for hours until “QWERTY” appears on our foreheads then we’ll finally realize the issue is because some tested value actually is [Blank].


There are two ways to minimize the embarrassing “QWERTY”-on-forehead syndrome:

  1. Begin relying on the Test feature for the expression to confirm expected results. This may require many test rows to be added to work well - especially in a new app.

  2. Explicitly test for [Blank] and not [Blank] - where it may occur. At the very least this will help remind you of a possible [Blank] value when reviewing the expression in the future. An example would be:

AND(ISNOTBLANK(<<value to test>>), <<value to test>> <= 100)

If you wanted to accept [Blank] as a valid value, you MUST test for it anyway:

OR(ISBLANK(<<value to test>>), <<value to test>> <= 100)
1 Like

With the values as indicated by the OP, yes, you do.
But with the one change that I mentioned, I’m pretty sure that no, you don’t.

How so?

Hi @WillowMobileSystems and @Marc_Dillon

The goal is to represent blank as a single unique value that is not equal to the default value (in the case of Number, this is zero), but acts like default value in non-equality comparisons (<, >). Once this is understood, I believe the new behavior in the table is consistent with itself. So, to answer the one of the questions asked, [Blank] < 1.0 will result in true. Blank and empty string will continue to be treated the same.

This behavior may not be perfect for everyone, but we thought it that it would be sensible enough in practical usage.

If blank values in comparisons were to always return false, then we would be introducing inconsistency elsewhere since Blank > 1 and Blank <= 1 would both be false despite being inversions that are supposed to have opposite values.

If blank values are unable to participate in comparisons with numbers and instead return neither Yes or No but a third “unknown” value like SQL databases, this would make the Yes/No type more complicated.

3 Likes

I guess there really is no perfect implementation or we wouldn’t have different ways these outlier values are treated currently in other languages/platforms.

I still think there will be confusion but at least server and client will behave the same which is the most important enhancement with this change!!

4 Likes

Consider these 2 questions:

  1. “Is blank less than 0?”
  2. “Is blank less than 1?”

What would be your answers to those 2 questions?
I would say “no” in both cases.
The “consistent” results from the OP apparently would say:

  1. “no”
  2. “yes”

I would call that inconsistent.

Now consider these 2 expressions:

AND( ISNOTBLANK( [val] ) , [val] < 0 )
AND( ISNOTBLANK( [val] ) , [val] < 1 )

For the first expression, with a blank value, and regardless of your answer for #2 above, both terms would be false, i.e., they’d agree. At which point, there’s no reason to include the ISNOTBLANK() term.

For the second expression, with a blank value, if you answered “yes” to #2, the terms do not agree, and so it is required to include the first term to keep the same functionality. But if you answered “no” again, then it is the same as above, the terms agree and thus there’s no reason to use the ISNOTBLANK term.

Obviously this is just one use-case. My “pretty sure” was alluding to the fact that I can’t possibly think of all use-cases. But all that I have thought of would benefit from my desired behavior, and not force the checking for blank values in every expression.


Interesting. I did not realize it was a requirement for inversions to have opposite values. I went to go check some results in my browser’s javascript console just now, and got this:
image

So I suppose what was originally written is consistent with other platforms too.

I still hold my belief as expressed above, but I guess it seems I’m arguing against the convention of a very major programming language as well. I don’t know what to think anymore now that I just checked that, haha.

For sure.

3 Likes

The discussion is probably mute now but I think there was a mis-understanding. Agreeing with you I was using the assumption that your suggestion was being used where any test between a number and [Blank] returns FALSE. In this case, if we wanted to also allow [Blank] as a valid value we would need to test for ISBLANK() like in the OR() example I supplied above. Did I miss something?

1 Like

This should 100% be pinned on the forum so that it cannot be below the first page when you open it up as it has a profound affect on pretty much every app. Also should be an un-ignorable notification when it is added since it has such a big impact.

My opinion:

  • A blank textual value should be equivalent to an empty string ("")

  • A blank numeric value should be equivalent to zero.

  • A blank Yes/No value should be equivalent to FALSE.

  • Blank List or EnumList: an empty list.

  • Blank Time: 00:00:00 (midnight)

  • Date: first date of the epoch

  • DateTime: midnight of the first day of the epoch

  • Duration: 000:00:00

Alternatively, a blank value in a value-to-value comparison should be a fatal error.

6 Likes

Also given how little of my time I can devote to the appsheet side of our systems, is there an end of life timeframe for the Legacy option? Just to put on our timeline.

What would be your opinion of the output of this situation:

ANY( Slice[Number-column] )

…where the Slice has no rows in it.

100% agree

100% agree

66% agree but the other 34% is not against this.

I think the key is just clear understanding and explanation of the outcomes for all situations that involve a possible blank value. It’s not necessarily a problem that I need to use OR(ISBLANK())'s. It’s the fact that it’s not inherently obvious that I need to use them.

2 Likes

ANY( empty-list ) should return nothing, producing a blank value.

1 Like

Hi @natalie the “Learn more” opens in the same tab. I think it should open in a new tab.
image

3 Likes

Thanks for the suggestion! It should be fixed in the first deployment of next week.

2 Likes