A cautionary tale about legacy tables and the "auto" column

App Building
1

I made an unfortunate discovery this morning -- a teammate was gathering numeric unique PKID values from a table in our Retool app and using them to associate other data by ID in our system. What we had not realized was the column containing these PKID values was set to "auto." When it's auto, Retool tries to treat the number as numeric instead of text, and in doing so, it seemingly also applied rounding to these values.

Displayed value: 4854400000045288000
Expected value: 4854400000045288143

The question for us now becomes: how long have we been feeding bad IDs into other systems?

While I can appreciate that Retool is trying to enable sorting on numerical values by default, having "auto" columns perform rounding is an unsafe default. It should not behave this way.

Update: this behavior seems to be specific to legacy tables, as I am not seeing an "auto" column option on new tables.

2

Hi @kevinsplx Thank you for reaching out about this! I'm sorry to hear this automated setting caused a disruption :disappointed:

The legacy table doesn't support big integers. You'll see a similar issue you in Javascript queries & transformers if you try to return 4854400000045288143 as a number. Generally, the workaround is to stringify the number when working with it in Retool.

However, when we created the new table, we added a #BigInt column type. On my side, I noticed that the new table still defaulted to showing 4854400000045288143 as a number in a #Number column (4854400000045288000), so I had to manually change the column type to #BigInt: