Nonce arithmetic and types in sqlite

[lrettig]

https://github.com/spacemeshos/go-spacemesh/blob/dd50f53d404055568c823748503b2d9427cbad38/sql/transactions/transactions.go#L187

On this line, and a few other places in this file, uint64 nonces are converted to int64 without checking for overflow. Can we add overflow protection here?

[dshulyak]

Thanks i forgot to open a bug for that, it is not a problem if you just store value as int64 and cast it back to uint64 when loaded. But if you sort them or doing some arithmetic it will be a problem.

We need to implement a custom sql function for nonces to support proper order or store it as 8 byte big endian explicitly.

[countvonzero]

moving this out of pm project as this is a general database bug

[evt]

As @countvonzero pointed out, there are multiple issues still:

• we cannot do a direct nonce comparison with int in sql queries anymore because blob uses memcomp() e.g. select * from txs where nonce > 100
• we cannot do arithmetic on the nonce. e.g. select 2*nonce from txs or select nonce+1 from txs

Granted these examples are not applicable now, but it's confusing/non-intuitive.

Additionally, we have other uint64 in the database: rewards, account balance/next nonce, tick count, and base tick height. they are treated as int64 in sqlite and we still cannot do comparison on them in db.

[dshulyak]

this was a bug about nonce, please open another issue with specific description