Changing 'belongs to' opinion for a taxon incorrectly adds a bunch of "ghost" alternative names

[aazaff]

If you take GENUS A belonging to FAMILY B and reassign it to FAMILY C, then the taxon info display for GENUS A will incorrectly list all taxa previously edited by the user as an "alternative name".

[NoisyFlowers]

To recreate:

1. find a genus that does not produce any alternative "Alternative spellings" in the classic taxon search results. Get that genus's taxon_no from authorities.
2. find a family to move it into. Just search authorities for taxon_rank family and pick one
3. Enter the taxon_no from (1) as child_no and the taxon_no from (2) as parent_no in the POST body below and execute.
4. There will now be a bunch of nonsensical Alternative spellings on the results page when refreshed.

This is an example of an opinion POST that causes this problem:

{
  "opinion": {
    "reference_no": 75925,
    "child_no": 26891,
    "status": "belongs to",
    "basis": "stated with evidence",
    "spelling_reason": "original spelling",
    "parent_no": 84994,
    "ref_has_opinion": "YES",
    "author1init": "A",
    "author1last": "Zaffos",
    "pubyr": 2016
  },
  "allowDuplicate": false,
  "allowMigrations": false
}		

The select that accumulates the alternative spellings is at https://github.com/paleobiodb/classic/blob/1cc4d2748c339856c9263743a4bb6c6e1372ad8b/lib/PBDB/TaxonInfo.pm#L3881

Here it is. $taxon_no gets the child_no from above. $auth->{'taxon_name'} gets its name from authorities.

	SELECT 
		a.taxon_no,
		taxon_name,
		taxon_rank 
	FROM 
		authorities a,
		taxa_tree_cache t 
	WHERE 
		a.taxon_no = t.taxon_no AND 
		synonym_no = $taxon_no AND 
		spelling_no = synonym_no AND 
		t.taxon_no != spelling_no AND 
		a.taxon_name != '".$auth->{'taxon_name'}."' 
	ORDER BY 
		taxon_name";

Note that this select uses taxa_tree cache. I don't know what that is. I don't maintain it in any way. But it does appear to change when I run the opinion POST. And it changes in such a way as to cause the problem.

Let's try it.

We'll pick on genus Naticonema, taxon_no 9004. We'll move it into family Melyandrida, taxon_no 69492.

If we plug in 9004 and Naticonema to the select above, we get no results. We can also search the taxa_tree_cache directly on the synonym_no

select 
	* 
from 
	pbdb.taxa_tree_cache
where 
	synonym_no  = 9004;

We get two records. One is for the taxon itself, the other for a synonym.

Ok, let's run the POST (using the swagger interface).

Now, when we run the select, we get this

Querying the taxa_tree_cache directly, we get:

Where in the world are all these records coming from? One clue is that they are all for taxons I've created or touched myself in testing. But I have no idea how they are getting in here. As I said at the top, I don't maintain taxa_tree_cache.

I see no triggers that would cause this. Is there maybe something running in the mariadb docker image that would update this as soon as I run the POST?

[aazaff]

That's an interesting question. I don't see any way to find out other than asking Michael.