receiver_deployment_id associated with multiple receiver_name in valid_detection table

[xhoenner]

Steps to reproduce:

• Create receiver named R1
• Create a deployment D with receiver R1 and create also a recovery
• Upload detections corresponding to receiver R1
• Make sure detections are valid for R1 (select count(*) from valid_detections where receiver_name='R1')
• Create receiver named R2
• Associate deployment D (same one from above) with receiver R2
• Upload detections corresponding to receiver R2

What happens:
Deployment D will contain both detections from receiver R1 and receiver R2.

In production, some receiver_deployment_id records are associated with different receiver names, to see duplicate receivers for a deployment, you can run:

WITH a AS (SELECT DISTINCT receiver_deployment_id, receiver_name FROM aatams.valid_detection),
b AS (SELECT receiver_deployment_id, COUNT(*) FROM a GROUP BY receiver_deployment_id)
SELECT DISTINCT a.receiver_deployment_id, a.receiver_name
FROM b
JOIN a ON b.receiver_deployment_id = a.receiver_deployment_id
WHERE b.count > 1
ORDER BY a.receiver_deployment_id

What should happen:
Only detections from receiver R2 should be valid for deployment D, in other words, each receiver_deployment_id should be associated with a unique receiver_name.

[jkburges]

@xhoenner here's the query I used:

select * from (
    select distinct 
        receiver_deployment_id,
        count(distinct receiver_name) as distinct_receivers_count,
        array_to_string(array_agg(distinct receiver_name), ',') as receiver_names
    FROM valid_detection
    group by receiver_deployment_id
    order by receiver_deployment_id
) distinct_receivers
where distinct_receivers_count > 1

to get results:

receiver_deployment_id,distinct_receivers_count,receiver_names
673062,2,"VR2W-103355,VR3UWM-354"
853255,2,"VR2W-101711,VR2W-101778"
2854249,2,"VR2W-103311,VR2W-109647"
29838646,2,"VR2W-110432,VR2W-110433"

Does this match with what you see?

[xhoenner]

@jkburges yes it does, see below:

[jkburges]

@danfruehauf I've got a pretty good idea of what's happened here now I think. Let's look at the last couple: receivers VR2W-110432 and VR2W-110433

Run this query:

select * from aatams.audit_log
where new_value like '%110432%'
or new_value like '%110433%'
order by date_created

You will see that for audit log having id 84440353, a deployment's receiver was changed from VR2W-110432 to VR2W-110433.

My guess is that the sequence of events is thus:

1. Deployment created for receiver VR2W-110432
2. First CSV file uploaded
3. Existing deployment edited to now be for VR2W-110433
4. Second CSV file uploaded.

Voila, we have the situation described above.

I bet we can re-create this pretty easily without having to load production data. I can help you with this tomorrow.

Note: the audit log feature wasn't in there from the start, so some of the smaller ids don't have corresponding audit log entries.

[jkburges]

In terms of fixing it, there are two parts:

1. Stopping this inconsistency from happening.
   The best solution IMO is Ideas for improving AATAMS' performance #72 point 3.
   Otherwise, we would need to "re-process" existing detections, both valid and invalid, whenever a deployment or recovery is created, edited or deleted - pretty messy I think you will agree.
2. Cleaning up what's there now.
   If we implement the "best" solution, then there would be no cleaning up. Else, we will need to make sure we re-process any of the affected detections.

[danfruehauf]

@jkburges I managed to reproduce the problem. Should I change the description of this bug?

[jkburges]

Yes, I would change the "Steps to reproduce" in the OP given what we now know, and move the SQL to under "what happens", i.e. how you can tell there's a problem.

[jkburges]

@pblain @danfruehauf I don't think we should attempt to fix this without first discussing the pros and cons of the possible fixes mentioned in #133 (comment)

[danfruehauf]

Part of why I returned it to the board. I think it was a bit too big for me to attempt and fix it as one of my first AATAMS tasks.

[pblain]

In that case we should remove it from the board and replace with a more manageable bug to fix. It doesn't matter which, as long as we keep ratcheting them down.