Add tests for https://gitlab.com/caosdb/caosdb-server/-/issues/145

Summary

Tests for caosdb-server!62 (merged) (https://gitlab.com/caosdb/caosdb-server/-/issues/145) and also https://gitlab.com/caosdb/caosdb-server/-/issues/147

Please merge together with caosdb-server!62 (merged)

Focus

The MR caosdb-server!62 (merged) proposed to remove the "casting to integer". These tests show that that would be a bug and also test the fix for https://gitlab.com/caosdb/caosdb-server/-/issues/145

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 (or not necessary)
• 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 (or not necessary)
• 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.

tests/test_issues_server.py

@@ -870,10 +870,6 @@ def test_145():
     assert rec2.get_property("TestPropDouble").value == 20_000_000_000
     assert isinstance(rec2.get_property("TestPropDouble").value, float)
 
-    assert db.execute_query("FIND TestRT WITH TestProp < 1000000000.2",
-                            unique=True).id == rec1.id  # this is the integer property
-    assert db.execute_query("FIND TestRT WITH TestProp > 1000000000.2", unique=True).id == rec2.id
-
     assert db.execute_query("FIND TestRT WITH TestProp = 1000000000", unique=True).id == rec1.id
     assert db.execute_query("FIND TestRT WITH TestProp = 1000000000.0", unique=True).id == rec1.id
 
@@ -889,3 +885,61 @@ def test_145():
     assert db.execute_query(
         "FIND TestRT WITH TestPropInt < 10000000000000000000000000000000000000000000000000000000000",
         unique=True).id == rec1.id
+
+
+@pytest.mark.xfail(reason="Fix https://gitlab.com/caosdb/caosdb-server/-/issues/147")
+def test_147():
+    """Searching for integer numbers results in wrong results if floats are used.
+
+    https://gitlab.com/caosdb/caosdb-server/-/issues/147
+    """
+    db.Property("TestProp", datatype=db.TEXT).insert()
+    db.Property("TestPropInt", datatype=db.INTEGER).add_parent("TestProp").insert()
+
+    db.RecordType("TestRT1").insert()
+    db.RecordType("TestRT2").insert()
+    rec1 = db.Record("TestRec1").add_parent("TestRT1").add_property(
+        "TestPropInt", 1).insert()
+    assert rec1.get_property("TestPropInt").value == 1
+    assert isinstance(rec1.get_property("TestPropInt").value, int)
+    rec2 = db.Record("TestRec2").add_parent("TestRT2").add_property(
+        "TestPropInt", -2).insert()
+
+    # Find the records
+    assert db.execute_query("FIND TestRT1 WITH TestProp < 1.9", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp < 1.1", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp = 1.0", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp > 0.9", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp > 0.1", unique=True).id == rec1.id
+
+    assert db.execute_query("FIND TestRT1 WITH TestProp <= 1.9", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp <= 1.1", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp >= 0.9", unique=True).id == rec1.id
+    assert db.execute_query("FIND TestRT1 WITH TestProp >= 0.1", unique=True).id == rec1.id
+
+    assert db.execute_query("FIND TestRT2 WITH TestProp < -1.1", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp < -1.9", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp = -2.0", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp > -2.1", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp > 2.9", unique=True).id == rec2.id
+
+    assert db.execute_query("FIND TestRT2 WITH TestProp <= -1.1", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp <= -1.9", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp >= -2.1", unique=True).id == rec2.id
+    assert db.execute_query("FIND TestRT2 WITH TestProp >= 2.9", unique=True).id == rec2.id
+
+    # Don't find the records
+    assert len(db.execute_query("FIND TestRT1 WITH TestProp < 0.9")) == 0
+    assert len(db.execute_query("FIND TestRT1 WITH TestProp <= 0.9")) == 0
+    assert len(db.execute_query("FIND TestRT1 WITH TestProp > 1.1")) == 0
+    assert len(db.execute_query("FIND TestRT1 WITH TestProp >= 1.1")) == 0
+
+    assert len(db.execute_query("FIND TestRT2 WITH TestProp > -1.9")) == 0
+    assert len(db.execute_query("FIND TestRT2 WITH TestProp >= -1.9")) == 0
+    assert len(db.execute_query("FIND TestRT2 WITH TestProp < -2.1")) == 0
+    assert len(db.execute_query("FIND TestRT2 WITH TestProp <= -2.1")) == 0
+
+    # Smaller numbers, but querying across number types.
+    rec3 = db.Record("TestRec3").add_parent("TestRT").add_property("TestPropInt", 1).insert()
+    assert db.execute_query("FIND TestRT WITH TestPropInt < 2", unique=True).id == rec3.id
+    assert db.execute_query("FIND TestRT WITH TestPropInt < 2.5", unique=True).id == rec3.id