Fix for caosdb-server#154

Summary

Fix for caosdb-server#154 (closed)

Focus

Bug was in apply POV: If the operator is '!=' we need to check for "NOT EXISTS", while "EXISTS" corresponds to the "=" operator.

Test Environment

Test are in caosdb-pyinttest!13 (merged)

Check List for the Author

Please, prepare your MR for a review. Be sure to write a summary and a focus and create gitlab comments for the reviewer. They should guide the reviewer through the changes, explain your changes and also point out open questions. For further good practices have a look at our review guidelines

• All automated tests pass
• Reference related Issues
• Up-to-date CHANGELOG.md
• Annotations in code (Gitlab comments)
  • Intent of new code
  • Problems with old code
  • Why this implementation?

Check List for the Reviewer

• I understand the intent of this MR
• All automated tests pass
• Up-to-date CHANGELOG.md
• The test environment setup works and the intended behavior is reproducible in the test environment
• In-code documentation and comments are up-to-date.
• Check: Are there specifications? Are they satisfied?

For further good practices have a look at our review guidelines.

procedures/query/applyPOV.sql

@@ -214,7 +214,7 @@ POV_LABEL: BEGIN
             IF o = "!=" AND refIdsTable IS NOT NULL THEN
                 SET existence_op = "NOT EXISTS";
             END IF;
-            SET sRefData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `reference_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_reference_data`', IF(refIdsTable IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `reference_data` AS subdata WHERE ', existence_op, ' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_reference_data` AS subdata WHERE ', existence_op, ' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1)')));
+            SET sRefData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `reference_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_reference_data`', IF(refIdsTable IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `reference_data` AS subdata WHERE ', existence_op, ' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) AND subdata.status != "REPLACEMENT" UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_reference_data` AS subdata WHERE ', existence_op, ' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) AND subdata.status != "REPLACEMENT"')));
             SET sDoubleData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, _get_head_iversion(subdata.entity_id) AS _iversion, subdata.property_id FROM `double_data` AS subdata UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_double_data` ', IF(vDouble IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id), property_id FROM `double_data` AS subdata WHERE ', getDoubleWhereClause(vDouble,unit_sig,vDoubleStdUnit,stdUnit_sig,o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_double_data` AS subdata WHERE ', getDoubleWhereClause(vDouble, unit_sig, vDoubleStdUnit, stdUnit_sig, o))));
             SET sIntData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, _get_head_iversion(subdata.entity_id) AS _iversion, subdata.property_id FROM `integer_data` AS subdata UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_integer_data`', IF(vInt IS NULL AND vDoubleStdUnit IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `integer_data` AS subdata WHERE ', getDoubleWhereClause(vInt, unit_sig, vDoubleStdUnit, stdUnit_sig, o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_integer_data` AS subdata WHERE ', getDoubleWhereClause(vInt, unit_sig, vDoubleStdUnit, stdUnit_sig, o))));
             SET sDatetimeData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `datetime_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_datetime_data`', IF(vDateTime IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o))));
@@ -227,7 +227,7 @@ POV_LABEL: BEGIN
             IF o = "!=" AND refIdsTable IS NOT NULL THEN
                 SET existence_op = "NOT EXISTS";
             END IF;
-            SET sRefData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data`', IF(refIdsTable IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data` AS subdata WHERE ',existence_op ,' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1)')));
+            SET sRefData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data`', IF(refIdsTable IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data` AS subdata WHERE ',existence_op ,' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) AND subdata.status != "REPLACEMENT"')));
             SET sDoubleData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, subdata.property_id FROM `double_data` AS subdata', IF(vDouble IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `double_data` AS subdata WHERE ', getDoubleWhereClause(vDouble,unit_sig,vDoubleStdUnit,stdUnit_sig,o))));
             SET sIntData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, subdata.property_id FROM `integer_data` AS subdata', IF(vInt IS NULL AND vDoubleStdUnit IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `integer_data` AS subdata WHERE ', getDoubleWhereClause(vInt, unit_sig, vDoubleStdUnit, stdUnit_sig, o))));
             SET sDatetimeData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data`', IF(vDateTime IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o))));