We're updating the issue view to help you get more done. 

Vault Queries across LinearStates and FungibleState tables return incorrect results

Description

As a user of the Vault Query API,
I am unable to obtain the correct expected results when issuing composite queries using both FungibleAsset and LinearState tables.

The following code excerpts highlight the failures:
Pre-condition: inserted 2 LinearStates and 1 FungibleAsset state into the vault.

1 vaultService.queryBy<ContractState>()

correctly returns 3 states

1 2 val linearStateCriteria = LinearStateQueryCriteria() vaultService.queryBy<ContractState>(linearStateCriteria)

correctly returns 2 states.

1 2 val fungibleAssetStateCriteria = FungibleAssetQueryCriteria() vaultService.queryBy<ContractState>(fungibleAssetStateCriteria)

correctly returns 1 state.

However both the following composite queries return 0 states:

1 2 vaultService.queryBy<ContractState>(linearStateCriteria.or(fungibleAssetStateCriteria)) vaultService.queryBy<ContractState>(linearStateCriteria.and(fungibleAssetStateCriteria))

If we examine the generated Hibernate SQL for the latter query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 select vaultschem0_.output_index as output_i1_35_0_, vaultschem0_.transaction_id as transact2_35_0_, vaultschem1_.output_index as output_i1_31_1_, vaultschem1_.transaction_id as transact2_31_1_, vaultschem2_.output_index as output_i1_33_2_, vaultschem2_.transaction_id as transact2_33_2_, vaultschem0_.consumed_timestamp as consumed3_35_0_, vaultschem0_.contract_state_class_name as contract4_35_0_, vaultschem0_.lock_id as lock_id5_35_0_, vaultschem0_.lock_timestamp as lock_tim6_35_0_, vaultschem0_.notary_name as notary_n7_35_0_, vaultschem0_.recorded_timestamp as recorded8_35_0_, vaultschem0_.state_status as state_st9_35_0_, vaultschem1_.issuer_name as issuer_n3_31_1_, vaultschem1_.issuer_ref as issuer_r4_31_1_, vaultschem1_.owner_name as owner_na5_31_1_, vaultschem1_.quantity as quantity6_31_1_, vaultschem2_.external_id as external3_33_2_, vaultschem2_.uuid as uuid4_33_2_ from vault_states vaultschem0_ cross join vault_fungible_states vaultschem1_ cross join vault_linear_states vaultschem2_ where vaultschem0_.output_index=vaultschem2_.output_index and vaultschem0_.transaction_id=vaultschem2_.transaction_id and vaultschem0_.output_index=vaultschem1_.output_index and vaultschem0_.transaction_id=vaultschem1_.transaction_id and vaultschem0_.state_status=? limit ?

we can see that the WHERE clause is expecting to find ALL the vault states (3) in both the joined tables.

This issue also means it is not possible to use the participants filter query criteria to obtain all Fungible And Linear states from a single query.

Status

Assignee

Jose Coll

Reporter

Jose Coll

Labels

None

Affected OS

None

Severity

Medium

Target Version/s

Corda Enterprise 3.2
Corda 3.3

Feature Team

Corda Core

Fix versions

Affects versions

Corda 3.2

Priority

Medium