Lisk-sdk: Logic.account.get rank not working

[LucasIsasmendi]

~Parent #1293~

Expected behavior

The field rank should show the last position (rank = 102) and default value should be null

Actual behavior

rank = 1

Steps to reproduce

Execute npm run mocha test/functional/system/blocks/chain.deleteLastBlock.js on 1.0.0 branch with only (type 2) register delegate

[robladbrook]

Technically, rank is working exactly as it should be (with regards to the query).

Because rank is calculated by row_number() OVER (ORDER BY a."vote" DESC, a."publicKey" ASC)::int the row_number() will be calculated for each row in the query result set. So if the query asks for 101 accounts, they will each get a row_number() between 1 and 101.

But if the query is limited to a single account where address = 'BLAH' then row_number() will return 1 since it is the only row in the result set - and therefore it's rank will also be 1.

Some potential approaches you can take to resolving this, listed in order of my preference:

1. Ignore rank (or set it to null) for queries with filters
2. Select the row_number() results in a sub-query and then apply the filter in the parent select (this could become VERY expensive depending on the growth of the mem_accounts table)
3. Persist the rank in the row and recalculate it each time it changes (this could become VERY expensive depending on the growth of the mem_accounts table and how volatile rank is)
4. Use a redis sorted set to track mem_accounts rank (overly complex for the problem at hand)

[LucasIsasmendi]

Thanks @robladbrook for the detailed list, we will go with option 2 for now.
If the query is very slow and impacts the overall performance, we can study apply part of the solution reverted by https://github.com/LiskHQ/lisk/pull/1225