Eos: getting table row with secondary key(--key-type sha256) returned wrong result

[elbowlicker]

I tried to get the row of the table using secondary key but inexpected row have been returned. I have tested with eosio v1.6.0-rc1

The following code is my rows of the table. The "user_hash" is the secondary key and it is "checksum256".

{
  "rows": [
    {
      "user_id": 0,
      "user_hash": "940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee7"
    },
    {
      "user_id": 1,
      "user_hash": "fdfa94e8850f7a8b2952ac6091215a201687ea7dd4791359ac03954a12acfa0c"
    },
    {
      "user_id": 2,
      "user_hash": "4951ed9dcdc34a39fcf1dae154475c64e385962ff6856c2069072a42fffb9081"
    }
  ],
  "more": false
}

I have tried to get the row with following command.
cleos get table codename scopename tablename --index 2 --key-type sha256 -L 940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee7

[taokayan]

Please add -U 940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee7 as well. Basically you need to specify the upper bound if you only want row(s) with exact key to be returned.

[elbowlicker]

Please add -U 940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee7 as well. Basically you need to specify the upper bound if you only want row(s) with exact key to be returned.

Thank you for answer. But unfortunately adding "- U 940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee7" did not solve the prolem. It didn't get any results. Also, I got the same result too when I added "-U 940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee8"

[taokayan]

Looks like the ordering is a little be complicated in checksum256. You can try adding --limit 1 to force it to return only one row, then check the returning hash.

[elbowlicker]

Looks like the ordering is a little be complicated in checksum256. You can try adding --limit 1 to force it to return only one row, then check the returning hash.

Following is the full parameters I have tried. But it still didn't work.

   { scope: 'inita',
     code: 'inita',
     table: 'user',
     json: 'true',
     lower_bound: '940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee7',
     upper_bound: '940ade173dce37480f6ab974a2e235f69e5128ba3804574ab10877bc5f503ee8',
     index_position: '2',
     encode_type: 'hex',
     key_type: 'sha256',
     limit: 1 },
  json: true };

[taokayan]

How about using the lower_bound and limit only but not the upper_bound?

[conr2d]

I wrote a patch for fixing this issue (#6591), but it's under review.

[taokayan]

@conr2d However this might break the existing app that relies on the current logic. @heifner do you have any idea on #6591 ?

[conr2d]

@taokayan This doesn't change how sha256 hash to be stored in multi_index, but only changes how to search rows with given hash value. (My patch is only related to chain_plugin) I think changing the way to make a query is not breaking existing behavior, because it didn't work in a right way.

[heifner]

I'm going to let @arhag review it. He is more familiar with that code.

[7-of-9]

+1

I've had to use a i128 key instead of sha256 key to be able to retrieve a single row by secondary index, which is a quite sub-optimal.

(Would be very helpful if when this fix is merged, the docs could also get an example of indexing by key_type sha256, with intuitive values working.)

[wangyilin1987]

mark

[jasonbert]

I believe this stackexchange post is relevant to this discussion. It's quite a nuisance that the value returned from a checksum256 property on a table row can't be directly used with the sha256 secondary index key type. At the moment the value requires processing before it can be used.

[gaboesquivel]

IMO the API code be responsible for reversing the sha for you, so that you can query using the sha256 string as is.

[cppfuns]

https://eosio.stackexchange.com/questions/4116/how-to-use-checksum256-secondary-index-to-get-table-rows
Is there an official solution to this problem? @heifner

[calebmcelroy]

Based on my own tests, this has been fixed in eosio 2.0. You can simply use the hash directly within lower_bound & upper_bound. Can anyone else confirm this?

[cppfuns]

@aclark-b1 When closing this question, can you give an accurate answer? This can help more people in need