The following SQL query should not return rows that have related TrackingShipmentItems: TrackingShipmentItems IS NULL AND ProcessingError != true AND created >= '2022-10-17'
Please describe the expected behavior of the issue, starting from the first action.
Run the SQL query above against the TrackingShipments table
Rows returned should not include any rows that have existing TrackingShipmentItems (this is a 1:n database relation column)
Actual Behavior
Please provide a description of what actually happens, working from the same starting point.
Run the SQL query above against the TrackingShipments table.
The query returns rows that have no TrackingShipmentItems relations, but also inexplicably returns a single row that does have TrackingShipmentItems (though all others are filtered out)
Reproducible Test Case
query: TrackingShipmentItems IS NULL AND ProcessingError != true AND created >= '2022-10-17'
@viktor.liablin That does work, I removed that part of the query and it was correctly filtering out the rows created before 2022-10-17. When I removed that part of the query all of the records from before that date showed up again, but the offending row I referred to was still there.
I was able to reproduce the issue. I am not sure if this is a bug or expected behavior (it doesn’t look right to me). I created an internal ticket for the development team to investigate the issue further (BKNDLSS-31302).
we have investigated the issue and found inconsistency with one relation. It is hard to say what cause this inconsistency, however, we have fixed the DB and now the result is correct, please check if everything is fine from your side.