Everest: duplicated txs

[menxit]

Ortelius returns duplicated txs on asset detail:

• https://explorerapi.avax.network/x/transactions?assetID=nznftJBicce1PfWQeNEVBmDyweZZ6zcM3p78z9Hy9Hhdhfaxm
• https://explorer.avax.network/asset/nznftJBicce1PfWQeNEVBmDyweZZ6zcM3p78z9Hy9Hhdhfaxm

[tyler-smith]

The cause of this issue is a faulty JOIN when filtering transactions by asset and/or address.

Relevant schema:

Transactions has many Outputs
Outputs has many OutputAddresses.

Outputs has asset_id property
OutputAddresses has address property 

SQL: https://github.com/ava-labs/ortelius/blob/40ca1f3237a5914f1417c0bf8a342aab66e77113/services/db/migrations/001_create_avm_schemas.up.sql

Bug analysis:

We JOIN Outputs and OutputAddresses to Transactions to enable a WHERE clause restricting asset_id and/or address, so transactions with multiple outputs or multiple addresses on an output become duplicated.

Code:

ortelius/services/indexes/avm/params.go

Lines 202 to 214 in 40ca1f3

	needOutputsJoin := len(p.Addresses) > 0 || p.AssetID != nil
	if needOutputsJoin {
	b = b.LeftJoin("avm_outputs", "avm_outputs.transaction_id = avm_transactions.id")
	}
	if len(p.Addresses) > 0 {
	addrs := make([]string, len(p.Addresses))
	for i, id := range p.Addresses {
	addrs[i] = id.String()
	}
	b = b.LeftJoin("avm_output_addresses", "avm_outputs.id = avm_output_addresses.output_id").
	Where("avm_output_addresses.address IN ?", addrs)
	}