Querying attachments with equals query is not really an equals query

Description

The attachments query api is not working in the way that you think it would.

An `equal` query made in `AttachmentQueryCriteria.AttachmentsQueryCriteria` performs an `IN` query under the hood. This differs from what most of us thought that the query actually achieved.

To expand on this, the `signersCondition` of `AttachmentQueryCriteria.AttachmentsQueryCriteria` takes in a `List<PublicKey>`. Therefore it is reasonable to assume that an equals query would return the attachments that have been signed by all the public keys in the list. That is not what happens.

Instead, a sql query like the below is executed:

```sql
select * from attachments join signers on attachments.id = signers.id where signers.signer in (A,B,C...)
```

Which returns any attachments that are found in the `IN` list. NOT the attachments which have the SAME signers that were passed into the query.

Examples will make this clearest:

```kotlin
val queryCriteria = AttachmentQueryCriteria.AttachmentsQueryCriteria(
signersCondition = Builder.equal(listOf(keyA, keyB, keyC)),
uploaderCondition = Builder.`in`(TRUSTED_UPLOADERS)
)
service.queryAttachments(queryCriteria)
```

This code will return any attachments that were signed by `keyA`, `keyB` or `keyC`. Including attachments signed by multiple keys, as long as one of the keys matches the ones passed into the query.

This query is not inherently bad and is actually a query that we want to be able to execute. But, this does not really match up to the functionality that you expect `equal` to have.

Following on from this, you can try to make an IN query but it will not work. The IN functionality has not been implemented, possibly due to EQUALS already doing what IN would do anyway. If we change EQUALS then IN also needs to be implemented to allow the current implementation of EQUALS to still be possible. The API for calling IN for an attachment query is also not very nice:

```kotlin
val queryCriteria = AttachmentQueryCriteria.AttachmentsQueryCriteria(
signersCondition = ColumnPredicate.CollectionExpression(CollectionOperator.IN, listOf(listOf(keyA, keyB, keyC), listOf(keyD),
uploaderCondition = Builder.`in`(TRUSTED_UPLOADERS)
)
service.queryAttachments(queryCriteria)
```

Notice the `listOf(listOf(keyA))` that has to be called. This does not make sense for an IN query here. That actually looks more like the input you would expect EQUALS to take in... Furthermore, again, `Builder.in` cannot be used here because `PublicKey` is not `Comparable`. The generic requirements of `in` are stopping this from working.

One last thing. The result of the attachment query does not search for distinct results. This should be simple to fix by adding `distinct` onto the `CriteriaBuilder`. Without this, if an attachment has multiple signers and matches to multiple keys passed into the EQUALS (technically IN) query, it returns the same attachment multiple times. There is no reason to do this since we only return the attachment id.

Status

Assignee

Unassigned

Reporter

Dan Newton

Priority

Medium

Labels

Severity

Medium

Fix versions

Ported to...

None

Feature Team

Corda Core

Story Points

8

Affects versions

Corda 4.3