After running some Insight imports, some objects have duplicated or more attribute values on object attribute that should only allow exactly 1 value (maximum cardinality = 1)

Issue Summary

After running some Insight imports, some objects have duplicated or more attribute values on object attribute that should only allow exactly 1 value (maximum cardinality = 1)

This is reproducible on Data Center: yes

Steps to Reproduce

N/A. This issue couldn't be reproduced on the UI, it usually happens after some Insight imports

Expected Results

All attributes that have maximum cardinality = 1 have only 1 value

Actual Results

There are 2 or more values on the attributes that have maximum cardinality = 1

Workaround

1. Follow the steps in Duplicated attribute values in Insight Objects with cardinality maximum 1
2. Run the SQL queries below to identify duplicated attribute and attribute values. If the SELECT statement returns something, run the DELETE statement. After that, run the same SELECT statement again (and DELETE if anything returns again) until it returns nothing.

   select * from "AO_8542F1_IFJ_OBJ_ATTR_VAL" where "OBJECT_ATTRIBUTE_ID" in (
   SELECT min(OA2."ID") AS TO_DELETE
   FROM "AO_8542F1_IFJ_OBJ_ATTR" OA2
            RIGHT JOIN (
       SELECT OTA."ID" as OTA_ID, OTA."NAME", OA."OBJECT_ID" as OA_OID, count(*)
       FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA
                LEFT JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
       GROUP BY OTA."ID", OTA."NAME", OA."OBJECT_ID"
       HAVING count(*) > 1) AS SUBQ ON OA2."OBJECT_TYPE_ATTRIBUTE_ID" = SUBQ.OTA_ID AND OA2."OBJECT_ID" = SUBQ.OA_OID
   GROUP BY OA2."OBJECT_ID", OA2."OBJECT_TYPE_ATTRIBUTE_ID");
   
   DELETE from "AO_8542F1_IFJ_OBJ_ATTR_VAL" where "OBJECT_ATTRIBUTE_ID" in (
   SELECT min(OA2."ID") AS TO_DELETE
   FROM "AO_8542F1_IFJ_OBJ_ATTR" OA2
            RIGHT JOIN (
       SELECT OTA."ID" as OTA_ID, OTA."NAME", OA."OBJECT_ID" as OA_OID, count(*)
       FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA
                LEFT JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
       GROUP BY OTA."ID", OTA."NAME", OA."OBJECT_ID"
       HAVING count(*) > 1) AS SUBQ ON OA2."OBJECT_TYPE_ATTRIBUTE_ID" = SUBQ.OTA_ID AND OA2."OBJECT_ID" = SUBQ.OA_OID
   GROUP BY OA2."OBJECT_ID", OA2."OBJECT_TYPE_ATTRIBUTE_ID");
   

   select * FROM "AO_8542F1_IFJ_OBJ_ATTR"
   where "ID" in (
   SELECT min(OA2."ID") AS TO_DELETE
   FROM "AO_8542F1_IFJ_OBJ_ATTR" OA2
            RIGHT JOIN (
       SELECT OTA."ID" as OTA_ID, OTA."NAME", OA."OBJECT_ID" as OA_OID, count(*)
       FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA
                LEFT JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
       GROUP BY OTA."ID", OTA."NAME", OA."OBJECT_ID"
       HAVING count(*) > 1) AS SUBQ ON OA2."OBJECT_TYPE_ATTRIBUTE_ID" = SUBQ.OTA_ID AND OA2."OBJECT_ID" = SUBQ.OA_OID
   GROUP BY OA2."OBJECT_ID", OA2."OBJECT_TYPE_ATTRIBUTE_ID");
   
   DELETE FROM "AO_8542F1_IFJ_OBJ_ATTR"
   where "ID" in (
   SELECT min(OA2."ID") AS TO_DELETE
   FROM "AO_8542F1_IFJ_OBJ_ATTR" OA2
            RIGHT JOIN (
       SELECT OTA."ID" as OTA_ID, OTA."NAME", OA."OBJECT_ID" as OA_OID, count(*)
       FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA
                LEFT JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
       GROUP BY OTA."ID", OTA."NAME", OA."OBJECT_ID"
       HAVING count(*) > 1) AS SUBQ ON OA2."OBJECT_TYPE_ATTRIBUTE_ID" = SUBQ.OTA_ID AND OA2."OBJECT_ID" = SUBQ.OA_OID
   GROUP BY OA2."OBJECT_ID", OA2."OBJECT_TYPE_ATTRIBUTE_ID");
   

3. When both SELECT statements above return nothing anymore, run an Insight reindex to keep the index updated.