WB Validation exception: get() returned more than one Picklist -- it returned 2

[maxpatiiuk]

Describe the bug
An exception occurred during data set validation process.
I did not have this happen before with this data set, so it might just be a database issue

To Reproduce

1. Upload data set
2. Use mappings that are suggested by the automaker
3. Try to validate the data set

Expected behavior
Validation completes

Screenshots

Desktop (please complete the following information):

• OS: macOS
• Browser Google Chrome
• Specify 7 Version: a11y branch

Additional context
Data set: test-workbench-data.csv.zip

Exception:

specify7-specify7-worker-1  | [2021-11-12 15:40:41,624: INFO/MainProcess] Received task: specifyweb.workbench.tasks.upload[7537d3a1-ef13-455d-a554-20be24a8e4fc]
specify7-specify7-worker-1  | [12/Nov/2021 15:40:43] [ERROR] [specifyweb.celery:30] Celery task failure!!!1
specify7-specify7-worker-1  | Traceback (most recent call last):
specify7-specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.6/site-packages/celery/app/trace.py", line 405, in trace_task
specify7-specify7-worker-1  |     R = retval = fun(*args, **kwargs)
specify7-specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.6/site-packages/celery/app/trace.py", line 697, in __protected_call__
specify7-specify7-worker-1  |     return self.run(*args, **kwargs)
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/tasks.py", line 41, in upload
specify7-specify7-worker-1  |     do_upload_dataset(collection, uploading_agent_id, ds, no_commit, allow_partial, progress)
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 113, in do_upload_dataset
specify7-specify7-worker-1  |     base_table, upload_plan = get_ds_upload_plan(collection, ds)
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 160, in get_ds_upload_plan
specify7-specify7-worker-1  |     return base_table, plan.apply_scoping(collection)
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/upload_table.py", line 29, in apply_scoping
specify7-specify7-worker-1  |     return apply_scoping(self, collection)
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 94, in apply_scoping_to_uploadtable
specify7-specify7-worker-1  |     toMany={f: [set_order_number(i, r.apply_scoping(collection)) for i, r in enumerate(rs)] for f, rs in ut.toMany.items()},
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 94, in <dictcomp>
specify7-specify7-worker-1  |     toMany={f: [set_order_number(i, r.apply_scoping(collection)) for i, r in enumerate(rs)] for f, rs in ut.toMany.items()},
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 94, in <listcomp>
specify7-specify7-worker-1  |     toMany={f: [set_order_number(i, r.apply_scoping(collection)) for i, r in enumerate(rs)] for f, rs in ut.toMany.items()},
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/tomany.py", line 20, in apply_scoping
specify7-specify7-worker-1  |     return apply_scoping(self, collection)
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 143, in apply_scoping_to_tomanyrecord
specify7-specify7-worker-1  |     wbcols={f: extend_columnoptions(colopts, collection, table.name, f) for f, colopts in tmr.wbcols.items()},
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 143, in <dictcomp>
specify7-specify7-worker-1  |     wbcols={f: extend_columnoptions(colopts, collection, table.name, f) for f, colopts in tmr.wbcols.items()},
specify7-specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 70, in extend_columnoptions
specify7-specify7-worker-1  |     picklist = picklistname and getattr(models, 'Picklist').objects.get(name=picklistname, collection=collection)
specify7-specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
specify7-specify7-worker-1  |     return getattr(self.get_queryset(), name)(*args, **kwargs)
specify7-specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/models/query.py", line 412, in get
specify7-specify7-worker-1  |     (self.model._meta.object_name, num)
specify7-specify7-worker-1  | specifyweb.specify.models.Picklist.MultipleObjectsReturned: get() returned more than one Picklist -- it returned 2!
specify7-specify7-worker-1  | [2021-11-12 15:40:43,244: ERROR/ForkPoolWorker-1] Task specifyweb.workbench.tasks.upload[7537d3a1-ef13-455d-a554-20be24a8e4fc] raised unexpected: MultipleObjectsReturned('get() returned more than one Picklist -- it returned 2!',)
s

Upload plan:

{
  "baseTableName": "collectionobject",
  "uploadable": {
    "uploadTable": {
      "wbcols": {},
      "static": {},
      "toOne": {
        "collectingevent": {
          "uploadTable": {
            "wbcols": {
              "startdate": "Start Date Collected",
              "enddate": "End Date Collected",
              "method": "Collection Method"
            },
            "static": {},
            "toOne": {
              "locality": {
                "uploadTable": {
                  "wbcols": {
                    "minelevation": "Min Depth (M)",
                    "maxelevation": "Max Depth (M)",
                    "latitude1": "Latitude1",
                    "latitude2": "Latitude2",
                    "longitude1": "Longitude1",
                    "longitude2": "Longitude2",
                    "latlongtype": "Lat Long Type"
                  },
                  "static": {},
                  "toOne": {
                    "geography": {
                      "treeRecord": {
                        "ranks": {
                          "Country": {
                            "treeNodeCols": {
                              "name": "Country"
                            }
                          },
                          "State": {
                            "treeNodeCols": {
                              "name": "State/Prov/Pref"
                            }
                          },
                          "Continent": {
                            "treeNodeCols": {
                              "name": "Continent/Ocean"
                            }
                          }
                        }
                      }
                    }
                  },
                  "toMany": {}
                }
              }
            },
            "toMany": {
              "collectors": [
                {
                  "wbcols": {},
                  "static": {},
                  "toOne": {
                    "agent": {
                      "uploadTable": {
                        "wbcols": {
                          "title": "Collector 1 Title",
                          "firstname": "Collector 1 First Name",
                          "middleinitial": "Collector 1 Middle Initial",
                          "lastname": "Collector 1 Last Name"
                        },
                        "static": {},
                        "toOne": {},
                        "toMany": {}
                      }
                    }
                  }
                },
                {
                  "wbcols": {},
                  "static": {},
                  "toOne": {
                    "agent": {
                      "uploadTable": {
                        "wbcols": {
                          "title": "Collector 2 Title",
                          "firstname": "Collector 2 First Name",
                          "middleinitial": "Collector 2 Middle Initial",
                          "lastname": "Collector 2 Last name"
                        },
                        "static": {},
                        "toOne": {},
                        "toMany": {}
                      }
                    }
                  }
                },
                {
                  "wbcols": {},
                  "static": {},
                  "toOne": {
                    "agent": {
                      "uploadTable": {
                        "wbcols": {
                          "title": "Collector 3 Title",
                          "firstname": "Collector 3 First Name",
                          "middleinitial": "Collector 3 Middle Initial",
                          "lastname": "Collector 3 Last Name"
                        },
                        "static": {},
                        "toOne": {},
                        "toMany": {}
                      }
                    }
                  }
                },
                {
                  "wbcols": {},
                  "static": {},
                  "toOne": {
                    "agent": {
                      "uploadTable": {
                        "wbcols": {
                          "title": "Collector 4 Title",
                          "firstname": "Collector 4 First Name",
                          "middleinitial": "Collector 4 Middle Initial",
                          "lastname": "Collector 4 Last Name"
                        },
                        "static": {},
                        "toOne": {},
                        "toMany": {}
                      }
                    }
                  }
                }
              ]
            }
          }
        },
        "accession": {
          "uploadTable": {
            "wbcols": {
              "accessionnumber": "Accession No."
            },
            "static": {},
            "toOne": {},
            "toMany": {}
          }
        },
        "cataloger": {
          "uploadTable": {
            "wbcols": {
              "lastname": "Cataloged by"
            },
            "static": {},
            "toOne": {},
            "toMany": {}
          }
        }
      },
      "toMany": {
        "determinations": [
          {
            "wbcols": {
              "determineddate": "ID Date",
              "typestatusname": "ID Status"
            },
            "static": {},
            "toOne": {
              "taxon": {
                "treeRecord": {
                  "ranks": {
                    "Class": {
                      "treeNodeCols": {
                        "name": "Class"
                      }
                    },
                    "Family": {
                      "treeNodeCols": {
                        "name": "Family"
                      }
                    },
                    "Genus": {
                      "treeNodeCols": {
                        "name": "Genus"
                      }
                    },
                    "Species": {
                      "treeNodeCols": {
                        "name": "Species",
                        "author": "Species Author"
                      }
                    },
                    "Subspecies": {
                      "treeNodeCols": {
                        "name": "Subspecies",
                        "author": "Subspecies Author"
                      }
                    }
                  }
                }
              },
              "determiner": {
                "uploadTable": {
                  "wbcols": {
                    "firstname": "Who ID First Name",
                    "title": "Determiner 1 Title",
                    "middleinitial": "Determiner 1 Middle Initial",
                    "lastname": "Determiner 1 Last Name"
                  },
                  "static": {},
                  "toOne": {},
                  "toMany": {}
                }
              }
            }
          },
          {
            "wbcols": {},
            "static": {},
            "toOne": {
              "determiner": {
                "uploadTable": {
                  "wbcols": {
                    "firstname": "Determiner 1 First Name"
                  },
                  "static": {},
                  "toOne": {},
                  "toMany": {}
                }
              }
            }
          }
        ],
        "preparations": [
          {
            "wbcols": {},
            "static": {},
            "toOne": {
              "preptype": {
                "mustMatchTable": {
                  "wbcols": {
                    "name": "Prep Type 1"
                  },
                  "static": {},
                  "toOne": {},
                  "toMany": {}
                }
              }
            }
          }
        ]
      }
    }
  }
}

[benanhalt]

This happens if the database has two picklists with the same name for a collection. There should be a uniqueness constraint on picklist name in the database schema to stop this from happening, but there isn't, so it does. Murphy.

[grantfitzsimmons]

Still having this issue. I have had it cause issues more than a couple of times, including today

[maxpatiiuk]

Open query builder
Run a query on picklist table
Look for two picklists with the same name
Delete one of them

[grantfitzsimmons]

I did! It should have a uniqueness constraint in the future though

[maxpatiiuk]

There is a duplicate pick list in FreshFish. Trying to make any changes to the duplicate causes a BusinessRuleException. Thus, there is some sort of uniqueness check.
Either duplicate pick list was created in an older Specify 7 version, or Specify 6 does not enforce uniqueness

[maxpatiiuk]

@benanhalt Andy pointed out that pick lists in Specify 6 are scoped down to a collection.
PIck list names must be unique within a collection, but don't have to be unique between collections.
Can you modify the business rules in Specify 7 to reflect that?

[benanhalt]

As you noted above, that business rule already exists. It can't do anything about duplicate name/collection pairs that are already in the database though.

[benanhalt]

If you change the duplicate to have a different name, you should be able to save it, I think.

[maxpatiiuk]

That is indeed the case.
I am saying that Specify 6's business rule enforces uniqueness within collection only, not within the entire database.

[benanhalt]

It is with respect to collection.

[benanhalt]

It could be that Specify 6 intentionally lets there be duplicates and then has some priority mechanism. E.g. user defined picklists take priority over system picklists. Or newer picklists take priority over older. I would have to dig into the Specify 6 source to find out.