VaultQueryException on every complex custom query request

[avasilevich]

Vault query requests to get all type-specific states works fine, but every complex custom query request fails with:

net.corda.core.node.services.VaultQueryException: An error occurred while attempting to query the vault: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: by near line 1, column 122 [select generatedAlias0, generatedAlias1 from net.corda.node.services.vault.VaultSchemaV1$VaultStates as generatedAlias0, by.aetsoft.schemas.IdentityCardSchemaV1$PersistentIdentityCard as generatedAlias1 where ( generatedAlias0.stateStatus=:param0 ) and ( generatedAlias0.contractStateClassName in (:param1) ) and ( ( generatedAlias0.stateRef=generatedAlias1.stateRef ) and ( generatedAlias1.id=:param2 ) ) and ( generatedAlias0.stateRef=generatedAlias1.stateRef )]

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: by near line 1, column 122 [select generatedAlias0, generatedAlias1 from net.corda.node.services.vault.VaultSchemaV1$VaultStates as generatedAlias0, by.aetsoft.schemas.IdentityCardSchemaV1$PersistentIdentityCard as generatedAlias1 where ( generatedAlias0.stateStatus=:param0 ) and ( generatedAlias0.contractStateClassName in (:param1) ) and ( ( generatedAlias0.stateRef=generatedAlias1.stateRef ) and ( generatedAlias1.id=:param2 ) ) and ( generatedAlias0.stateRef=generatedAlias1.stateRef )]

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: by near line 1, column 122 [select generatedAlias0, generatedAlias1 from net.corda.node.services.vault.VaultSchemaV1$VaultStates as generatedAlias0, by.aetsoft.schemas.IdentityCardSchemaV1$PersistentIdentityCard as generatedAlias1 where ( generatedAlias0.stateStatus=:param0 ) and ( generatedAlias0.contractStateClassName in (:param1) ) and ( ( generatedAlias0.stateRef=generatedAlias1.stateRef ) and ( generatedAlias1.id=:param2 ) ) and ( generatedAlias0.stateRef=generatedAlias1.stateRef )]

Query selection code:

private inline fun <reified U : ContractState> getState(
    services: ServiceHub,
    block: (generalCriteria: QueryCriteria.VaultQueryCriteria) -> QueryCriteria
): List<StateAndRef<U>> {
    val query = builder {
        val generalCriteria = QueryCriteria.VaultQueryCriteria(Vault.StateStatus.UNCONSUMED)
        block(generalCriteria)
    }
    val result = services.vaultService.queryBy<U>(query)
    return result.states
}

fun getIdentityCardById(ownerId: UUID, services: ServiceHub): StateAndRef<IdentityCard>? {
    val states = getState<IdentityCard>(services) { generalCriteria ->
        val uuidCriteria =
            QueryCriteria.VaultCustomQueryCriteria(IdentityCardSchemaV1.PersistentIdentityCard::id.equal(ownerId))
        generalCriteria.and(uuidCriteria)
    }
    return states.singleOrNull()
}

IdentityCardState

@BelongsToContract(IdentityCardContract::class)
data class IdentityCard(
    val firstName: String,
    val lastName: String,
    val host: Party,
    val identifier: UniqueIdentifier
) : LinearState, QueryableState {

    override val linearId: UniqueIdentifier
        get() = identifier

    override val participants: List<AbstractParty>
        get() = listOf(host)

    override fun generateMappedObject(schema: MappedSchema): PersistentState {
        return when (schema) {
            is IdentityCardSchemaV1 -> IdentityCardSchemaV1.PersistentIdentityCard(
                this.identifier.id,
                this.host.toString(),
                this.firstName,
                this.lastName
            )
            else -> throw IllegalStateException("Cannot construct instance of ${this.javaClass} from Schema: $schema")
        }
    }

    override fun supportedSchemas(): Iterable<MappedSchema> = listOf(IdentityCardSchemaV1)
}

IdentityCardSchemaV1 code

object IdentityCardSchemaV1 : MappedSchema(
    schemaFamily = IdentityCardSchema.javaClass,
    version = 1,
    mappedTypes = listOf(PersistentIdentityCard::class.java)
) {
    override val migrationResource: String
        get() = "idcard.changelog-master"

    @Entity
    @Table(name = "identity_cards")
    data class PersistentIdentityCard(
        @Column(name = "identifier", unique = true, nullable = false)
        val id: UUID,
        @Column(name = "host", unique = false, nullable = false)
        val host: String,
        @Column(name = "first_name", unique = false, nullable = false)
        val firstName: String,
        @Column(name = "last_name", unique = false, nullable = false)
        val lastName: String
    ) : PersistentState() {
        constructor() : this(UUID.randomUUID(), "", "", "")
    }
}

IdentityCardSchemaV1 changelog-v1

<changeSet author="R3.Corda" id="IdentityCardSchemaV1">
        <createTable tableName="identity_cards">
            <column name="output_index" type="integer">
                <constraints nullable="false"/>
            </column>
            <column name="transaction_id" type="varchar(64)">
                <constraints nullable="false"/>
            </column>
            <column name="identifier" type="UUID">
                <constraints nullable="false"/>
            </column>
            <column name="host" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="first_name" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="last_name" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey columnNames="output_index, transaction_id"
                       constraintName="PK_IdentityCardSchemaV1"
                       tableName="identity_cards"/>
</changeSet>

Node logs
node-logs.log

[nargas-ritu]

@avasilevich is it still an issue or it's resolved?