Option to allow limiting auto_random maximum value/range

[andreifedorov-bolt]

For some columns in databases, we use BIGINT UNSIGNED datatype. However, the following factors do not allow to use values that do not fit more than 53bits:

1. NodeJS number datatype
2. JSON number, only range of [-(2 ^ 53)+1, (2 ^ 53)-1] is guaranteed to work in JSON
   https://datatracker.ietf.org/doc/html/rfc7159#page-6

Examples:
% node -e 'console.log(JSON.stringify({"foobar": 18446744073709551615}))'
{"foobar":18446744073709552000}
% node -e 'console.log(JSON.parse("{"foobar": 18446744073709551615}"))'
{ foobar: 18446744073709552000 }

This causes issues if we convert autoincrement bigint unsigned to auto_random, as it almost immediately exceeds 53bit value by using the full range.
So limiting, e.g. AUTO_RANDOM(5, 6) instead of AUTO_RANDOM(5) (thus using only 6 bytes) might solve this issue.

This might be useful to allow to migrate back to auto_increment - if we limit the range when migrating to auto_random - so we have enough values free between the current and maximum.

[morgo]

This is a great point, thank you for the feature request.

I would actually say because it is random, there should not be a need to create an option. Not all users will be aware that numbers greater than 2^53-1 will be incorrectly handled in their apps.

[yiwen92]

Now auto_random only support int64, it exceed the range of JSON/NodeJS data type range then cause this issue.
As discussed with @tangenta, there are 2 ways to solve this:

1. provide int32 support auto_random, it is in the range of JSON data type;
2. provide preserved offset to set auto_random range more flexible. And for this 53-bits limitation, it can be set to auto_random(5, 11), 5 here means shared bits default setting(timestamp), and 11 means preserve 11 bits not used for auto-random.

[yiwen92]

@andreifedorov-bolt Hi, did you already use JSON format in TiDB? Since TiDB are also evaluting JSON support recently, could you please share your use scenario with us?

[andreifedorov-bolt]

@yiwen92 Solution #1 is not acceptable for us since we did run out of int32 for some columns long time ago and migrated to bigint. Solution #2 would be perfect.
JSON usage info will be shared separately.