Test fails in relational_operand due to test code, not functionality.

[larsrollik]

Bug Report

Description

Test fails in relational_operand.

Reproducibility

Run test container from datajoint/datajoint-python/docker-comose.yml with Python version 3.11 or 3.9 presets.

Stack trace from pytest:

_________________ TestDjTop.test_top_restriction_with_keywords _________________

self = <tests.test_relational_operand.TestDjTop object at 0x7563c4b042e0>
schema_simp_pop = Schema `djtest_relational`


    def test_top_restriction_with_keywords(self, schema_simp_pop):
        select = SelectPK() & dj.Top(limit=9, order_by=["select desc"])
        key = KeyPK() & dj.Top(limit=9, order_by="key desc")
        assert select.fetch(as_dict=True) == [
            {"id": 2, "select": 8},
            {"id": 2, "select": 6},
            {"id": 1, "select": 4},
            {"id": 2, "select": 4},
            {"id": 1, "select": 3},
            {"id": 1, "select": 2},
            {"id": 2, "select": 2},
            {"id": 1, "select": 1},
            {"id": 0, "select": 0},
        ]
>       assert key.fetch(as_dict=True) == [
            {"id": 2, "key": 6},
            {"id": 2, "key": 5},
            {"id": 1, "key": 5},
            {"id": 0, "key": 4},
            {"id": 1, "key": 4},
            {"id": 2, "key": 4},
            {"id": 0, "key": 3},
            {"id": 1, "key": 3},
            {"id": 2, "key": 3},
        ]
E       AssertionError: assert [{'id': 2, 'k...key': 4}, ...] == [{'id': 2, 'k...key': 4}, ...]
E         
E         At index 1 diff: {'id': 1, 'key': 5} != {'id': 2, 'key': 5}
E         Use -v to get more diff

tests/test_relational_operand.py:629: AssertionError

Additional Research and Context

Offending lines are these: https://github.com/datajoint/datajoint-python/blob/eef7e59316a05349dc662f34928403808d93e49c/tests/test_relational_operand.py#L631-632

Original:

        assert key.fetch(as_dict=True) == [
            {"id": 2, "key": 6},
            {"id": 2, "key": 5},  # <<
            {"id": 1, "key": 5},  # <<
            {"id": 0, "key": 4},
            {"id": 1, "key": 4},
            {"id": 2, "key": 4},
            {"id": 0, "key": 3},
            {"id": 1, "key": 3},
            {"id": 2, "key": 3},
        ]

Suggested fix: Swap lines for key==5:

        assert key.fetch(as_dict=True) == [
            {"id": 2, "key": 6},
            {"id": 1, "key": 5},
            {"id": 2, "key": 5},
            {"id": 0, "key": 4},
            {"id": 1, "key": 4},
            {"id": 2, "key": 4},
            {"id": 0, "key": 3},
            {"id": 1, "key": 3},
            {"id": 2, "key": 3},
        ]

Additional

Relevant for open PR #1197

[dimitri-yatsenko]

This is related to #1242. The order is not deterministic.

[dimitri-yatsenko]

This issue has been resolved in the DataJoint 2.0 codebase.

The test was updated to use order-independent comparison since dj.Top only guarantees which elements are selected, not their order when there are ties in the order_by column:

def test_top_restriction_with_keywords(self, schema_simp_pop):
    # dj.Top only guarantees which elements are selected, not their order
    select = SelectPK() & dj.Top(limit=9, order_by=["select desc"])
    key = KeyPK() & dj.Top(limit=9, order_by="key desc")
    # Convert to sets of tuples for order-independent comparison
    select_result = {tuple(sorted(d.items())) for d in select.to_dicts()}
    ...

The test now correctly validates the set of returned rows rather than assuming a specific order for rows with equal order_by values.

Related: #1242 (closed)

[dimitri-yatsenko]

Update: DataJoint 2.0 fixes dj.Top to properly preserve the ORDER BY clause in query results.

The test has been updated to verify ordering is preserved. To ensure deterministic results when there are ties, the test now uses a secondary sort key:

def test_top_restriction_with_keywords(self, schema_simp_pop):
    # dj.Top preserves the ORDER BY clause in results
    # Use secondary sort by 'id' to ensure deterministic ordering when there are ties
    select = SelectPK() & dj.Top(limit=9, order_by=["select desc", "id"])
    key = KeyPK() & dj.Top(limit=9, order_by=["key desc", "id"])
    assert select.to_dicts() == [...]
    assert key.to_dicts() == [...]

The fix ensures that:

1. dj.Top ordering is applied to fetch results (previously could be lost)
2. Tests use deterministic ordering with secondary sort keys to avoid tie-breaking ambiguity

Commit: test(Top): verify dj.Top preserves ORDER BY in results