Uploaded image for project: 'Corda'
  1. CORDA-1888

Vault Queries across LinearStates and FungibleState tables return incorrect results

    Details

    • Type: Bug
    • Status: Done (View workflow)
    • Priority: Medium
    • Resolution: Done
    • Affects Version/s: Corda 3.2
    • Fix Version/s: Corda 3.3
    • Component/s: None
    • Labels:
      None

      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.

      vaultService.queryBy<ContractState>()
      

      correctly returns 3 states

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

      correctly returns 2 states.

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

      correctly returns 1 state.

      However both the following composite queries return 0 states:

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

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

          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.

        Attachments

          Activity

            People

            • Assignee:
              Jose.Coll Jose Coll
              Reporter:
              Jose.Coll Jose Coll
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: