BUG: UNIQUEID() generated Exponential Values - is...

WillowMobileSys · 01-26-2023 08:56 AM

When a value is generated by UNIQUEID(), sometimes it will match a numeric exponential notation - e.g. "12345 e 12".

If the sheet column is set as "Automatic", the sheet will recognize this value as exponential notation upon saving and translates it to "1.2345E+12". Note: this translation does NOT happen when the value is copy/pasted into a sheet cell UNLESS the format is re-applied.

When AppSheet later retrieves the translated value, it not recognized as a valid key value.

NOW...I thought AppSheet had resolved this potential issue in some way so we don't have to constantly update the sheet default format on each and every table column we add that stores a key inside each and every app.

Is that not true? Or did they and the problem is back again?

EDITED: Searching again I did find an older post about a fix for this:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/New-Bug-Encountered-UNIQUEID-changed-to-scienti...

I haven't seen this issue for years until today!

SkrOYC

I don't have this particular issue, but as far as I recall, this was never solved.
If you ask me, this may be a GSheet problem rather than an AppSheet one, and also I think they expect us to change the cell formats on sheets based on the data that's going to be saved, so in this case changing the field to Text for PK/FK is a must

Marc_Dillon

I also don't believe this was ever fixed, and that it's not an Appsheet issue, but a spreadsheet issue.

Formatting the full table to "plain text" from the get-go has been my SOP for a long time.

WillowMobileSys

@Marc_Dillon wrote:

Formatting the full table to "plain text" from the get-go has been my SOP for a long time.

Good to know. I have always been concerned that doing so might impact other column types. I will probably apply this going forward

I was successful searching again for the post concerning the fix for this (I thought there was one). Here is the link to that article with a reply from Google staff:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/New-Bug-Encountered-UNIQUEID-changed-to-scienti...

I'll open this to AppSheet as a bug.

Marc_Dillon

Oh yah, I remember that now. 😄

WillowMobileSys

I also recognize that I am somewhat contradicting myself in that older post where I suggested that we, the app developer change the sheet columns to a suitable type.

But, man it has been nice to not have to worry about doing that at all!!

SkrOYC

@WillowMobileSys wrote:

I will probably apply this going forward

As a general comment, this seems to work fine for english-focused apps but not for all languages.

In my case (spanish) it saves the data in US format instead of my local format when the cell is formated as Text even if both the sheet and AppSheet table config is setup correctly.

So I just make sure to format cells on the most-compatible way

Koichi_Tsuji

Please upvote here, although this request not really collecting the interests from anyone.

https://www.googlecloudcommunity.com/gc/Feature-Ideas/UniqueID-expression-to-avoid-text-which-is-equ...

WillowMobileSys

Hey @Koichi_Tsuji !

Thanks for drawing our attention to your post. I upvoted!!

I have also submitted something to AppSheet Support.

In light of the fact that this was already addressed previously, as noted in the link I provided above, I believe this problem should be treated as a regression bug getting immediate attention.

This issue is infrequent but it can cause major problems if it occurs under the right circumstances.

It is probably not feasible to go through all the sheets used in all the apps from the past couple years to ensure this issue doesn't occur. So it seems the best fix at this point is for AppSheet to re-instate its solution to the UNIQUEID() function.

Steve

Note that "fixing" this on the AppSheet side substantially reduces the number of possible unique IDs that can be generated, thereby increasing the risk of duplicates. I agree that this is a spreadsheet problem, not an AppSheet one.

WillowMobileSys

@Steve wrote:

Note that "fixing" this on the AppSheet side substantially reduces the number of possible unique IDs

For a little fun...

When considering the actual count of values that need removed, it seems like its substantial. But since we are only concerned about removing the results with the letter "e" in the second position from the right, the overall difference is actually small:

For an 8 character Hex key (produced by UNIQUEID()), the difference in the number of combinations is:

16 x 16 x 16 x 16 x 16 x 16 x 16 x 16  --> Total possible combinations = 4.294967e+9
versus
16 x 16 x 16 x 16 x 16 x 16 x 15 x 16 --> Disallowing letter "e" = 4.026532e+9

Exponential Notation Pun fully intended!!

In other words... 4,294,967,000  versus 4,026,532,000

Because we lose precision with Exponential Notation, actual values are:

4,294,967,296 versus 4,026,531,840

Remember that we CAN re-use key values between tables, ao all the above means we can still have 4,026,531,840 happy co-existing rows in our table even if we were to ban the letter "e" from one of the key value positions.

************************

Now for more serious stuff...

I agree this is not an AppSheet problem. Its a Google Sheet problem! And who was the idiot at Google Sheets that decided it was a good thing to automatically convert values into Scientific Exponential Notation? Especially considering 1/2 of 1% of all scientists in the world actually use the damn notation!! (I made that last part up).

It is manual intensive to try to keep the sheets formatted to avoid this problem. I just ran into it today trying to fix some raw data by manually copying into a new sheet, fix and then copy back. Existing key values were converted. Luckily it's never very many that need to be corrected.

And considering that UNIQUEID() is just a special purpose function controlled by AppSheet, it just makes sense to help prevent the issue in the first place and put back the code that disallowed the problematic key values.

On a side note, I did submit to AppSheet support with the Link from AppSheet staff about resolving this. Support asked me for the app and steps to re-create! Huh??? Somehow I don't think a support person is going to generate 10,000 rows into a sample app until the Exponential Notation problem happens!!!

Koichi_Tsuji

My intention to open up this thread is not to discuss about workaround. I’m so now I zip my mouse.

yeah it is possible that it the problem is coming from Google sheet not from Appsheet. But from the users point of views, they should be the same , both are Google products and Appsheet os largely rely on Google sheet as it is suggested: so as far as the users arebroutinely see though problems, then permanent fix should be considered and introduced from my point of view. The one who can come up with workaround is advencef users alone. I m just saying to let Google consider a fix in order to avoid any possible problems where ANYONE may have encountered with Appsheet with or without realizing this problem is possibly happen.

SkrOYC

I don't want to discuss about it but I take this as the official response for any of this

Make an "AppSheet-friendly" spreadsheet - AppSheet Help
Effective use of column headers - AppSheet Help

Marc_Dillon

@WillowMobileSys wrote:

For an 8 character Hex key (produced by UNIQUEID()), the difference in the number of combinations is:
16 x 16 x 16 x 16 x 16 x 16 x 16 x 16  --> Total possible combinations = 4.294967e+9
versus
16 x 16 x 16 x 16 x 16 x 16 x 15 x 16 --> Disallowing letter "e" = 4.026532e+9
Exponential Notation Pun fully intended!!
In other words... 4,294,967,000  versus 4,026,532,000

Because we lose precision with Exponential Notation, actual values are:

4,294,967,296 versus 4,026,531,840

So that's about 260 million possible values that we'd lose.

I'm far from an expert with combinatorics (if that's even the right term), but I'm seeing a different way to calculate it. I calculated it the other way, by counting up the number of combinations that would get us into trouble. Basically, any number, then an e, then any other number. So that'd be 10 possible values across 7 positions, multiplied by 6 different positions that an e could be in (i.e. not beginning or last position). 10^7 * 6 = 60 million. Did I do it wrong?

SkrOYC

Btw, if we are on this, why not UNIQUEID("UUID")? I'm serious, that's my de facto for keys since like 10 apps ago, wrapped in LOWER()

WillowMobileSys

@Marc_Dillon wrote:

So that'd be 10 possible values across 7 positions, multiplied by 6 different positions that an e could be in (i.e. not beginning or last position). 10^7 * 6 = 60 million. Did I do it wrong?

Yes. But its close!

First as pointed out by @dbaum below, there are 2 positions where the "e" can create the exponential notation bit. I didn't realize and is good to know.

Second, it's not 10 possible values at each position, its 16. The keys are Hex - so 1,2,3,4,5,6,7,8,9,a,b,c,d,e,f are the possible values for each position.

To calculate values where "e" IS always in a position (static), you simply calculate the combination for all the other positions. In this case 16^7 ==>> 268,435,456

Since we now know there are 2 positions where "e" causes a problem, the number of lost combinations is double - one or the other but not both. The calculation is 2*16^7 ==> 536,870,912

The number of allowed combinations WITHOUT "e" is now ==>> 16^6 * 15^2 ==>> 3,774,873,600

I still think that is PLENTY of row key values for a single table. Do you not?

HOWEVER, just so everyone is aware, there is a performance concern if the pool of possible keys shrinks too small.

I am fairly certain AppSheet tests a generated key against the table to make sure it doesn't already exist. If it does then, the value is not accepted and a new value is generated. If the pool of possible keys shrinks small enough, the app may need to regenerate a value several times before finding a good one. That can lead to performance problems and gets worse as the table row size continues to grow.

Marc_Dillon

@WillowMobileSys wrote:

First as pointed out by @dbaum below, there are 2 positions where the "e" can create the exponential notation bit. I didn't realize and is good to know.

Second, it's not 10 possible values at each position, its 16. The keys are Hex - so 1,2,3,4,5,6,7,8,9,a,b,c,d,e,f are the possible values for each position.

I still think that is PLENTY of row key values for a single table. Do you not?

1. dbaum's test looks...odd. A very small sample size, doesn't really make any sense, doesn't match my previous experience. Not convincing of anything. I'm sticking with there being 6 positions of 'e' that can cause issue.

2. Yes, the number of possible values is 16, but the number of possible values in keys that we would remove, is only the 10 integers.

Yes, I think it's still enough values, even is the amount removed is your larger 260 million value.

WillowMobileSys

@Marc_Dillon wrote:

A very small sample size, doesn't really make any sense, doesn't match my previous experience.

I can't say I know for sure but I've never noticed it with the "e" in other positions.

@Marc_Dillon wrote:

Yes, the number of possible values is 16, but the number of possible values in keys that we would remove, is only the 10 integers.

Oh yeah, I see what you mean. It would be a lot fewer actual problem combinations. But I read that AppSheet was just testing if the "e" appeared in the position, then they skipped it. I was basing my calculation on the probable (easy) implementation. It would be complicated to try to skip ONLY the actual problematic values.

dbaum

FWIW, here's my results from directly typing values into Sheets columns formatted as Plain Text and as Automatic.

SkrOYC

I was thinking about this, that it was not just the 7th digit, the 6th could also generate problems

SkrOYC

@Marc_Dillon wrote:

dbaum's test looks...odd. A very small sample size, doesn't really make any sense, doesn't match my previous experience

More than a test, it was just showing that "e" could cause problems on either the 6th or 7th position, so "12345e99" could cause them

SkrOYC

Btw, my 2$ for the math class.
@WillowMobileSys is correct when it comes to total amount of combinations.

16 posible values with 8 characters is 16⁸, so in this case, knowing that an e causes problems on either the 6th or 7th position, it would be 15²x16⁶=3.774.873.600

SkrOYC

Btw, after spending more time into this, I get @Marc_Dillon's explanation, and it's true.

You need exactly numbers with just an e in the 6th or 7th position, so....

IMHO, 10 numbers in 7 of the 8 characters and removing 6 of the combinations where the e doesn't generate problems:
10⁷ = 10.000.000 10.000.000 - 6 = 9.999.994

So I think 9.999.994 are the combinations that could generate problems

But I guess the problem is what @WillowMobileSys mentioned:

@WillowMobileSys wrote:

It would be complicated to try to skip ONLY the actual problematic values.

Also, we can tell that there is a 0.2328% chance of having this problem, which is a lot if we think of this as way to reliably operating a software system.

I'll stick to my UNIQUEID("UUID") and suggest anyone to do the same thing, or at least format your worksheets PK/FK columns

Steve

@WillowMobileSys wrote:

I am fairly certain AppSheet tests a generated key against the table to make sure it doesn't already exist. If it does then, the value is not accepted and a new value is generated.

Not to my knowledge.

Fabian_Weller

When you add a new row by opening the form, there is an error message saying:
“There is already a row with the key ‘........’”
So you have to cancel the form and open it again.

dbaum

I hadn't come across that before--thanks for pointing it out. @lizlynch, this seems like a helpful detail to clarify in articles like What is a key? - AppSheet Help (which explicitly references the possibility of a duplicate key being added to a table) and UNIQUEID() - AppSheet Help (which explicitly references a non-zero possibility of a duplicate value being generated).

It would be helpful to also confirm whether/how that validation manifests when a row is created by means other than a form (e.g., action or AppSheet API).

WillowMobileSys

@Steve @Fabian_Weller @dbaum

I do recall a conversation with an AppSheet developer about no-chance of a dup when using UNIQUEID() (it may have been office hours or a post - I don't remember which). I may have rationalized in my head that they must check for dup when used in a key column.

It wouldn't be that difficult to do nor would it be much of a performance hit to guarantee dup-free keys. And since UNIQUEID() is an AppSheet function, there is no reason why they couldn't.

To snap back, the whole reason for this part of the conversation is that if AppSheet skips values that could result in Exponential Notation, the pool of possible key values reduces and the chances of a dup increases.

@dbaum wrote:

It would be helpful to also confirm whether/how that validation manifests when a row is created by means other than a form (e.g., action or AppSheet API).

I am aware of the message in the Form when a dup occurs. However, I believe this check does NOT occur when adding rows by actions. Instead AppSheet seems to allow the duplicate and then does a silent "cleanup" by removing one of the rows - I presume the older one. I have posted, in detail, about this behavior previously. To clarify, in that use case I was trying to use the ADD action to UPDATE a previous row because INPUT() was not available to the app. But I am certain the same behavior would occur for any row ADD if they happen to assign a key value that was already present - whether automatically by UNIQUEID() or otherwise - unless, of course, UNIQUEID() does inspect and skip dup key values.

Steve

Sure, but the generation of a new key that is unique isn't automatic--the user has to take action.

Fabian_Weller

@WillowMobileSys wrote:

However, I believe this check does NOT occur when adding rows by actions. Instead AppSheet seems to allow the duplicate and then does a silent "cleanup" by removing one of the rows - I presume the older one.

I have this setup in one of my apps. I use an action of type "add a new row to another table using values from this row".
When the Key already exists, it will override the values in that row. It will not delete the row and create a new row. So it's like an edit of the row.
In my usecase this is the desired behavior.

BUG: UNIQUEID() generated Exponential Values - issue has returned after previous fix.

Make an "AppSheet-friendly" spreadsheet - AppSheet HelpEffective use of column headers - AppSheet Help

Make an "AppSheet-friendly" spreadsheet - AppSheet Help
Effective use of column headers - AppSheet Help