History doesn't always pick the right record if same timestamp
When searching on a record and with _datetime set on context and using an order by it returns invalid values.
This is the data on the model table:
id | create_date | write_date | sequence | name
----+----------------------------+----------------------------+----------+-----------
75 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 1 | Any
77 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 3 | Mes
76 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 4 | Productes
And this is the data on my __history table:
id | __id | create_date | write_date | sequence | name
----+------+----------------------------+----------------------------+----------+-----------
75 | 237 | 2014-04-23 15:31:35.191993 | | 1 | Any
76 | 238 | 2014-04-23 15:31:35.191993 | | 2 | Productes
77 | 239 | 2014-04-23 15:31:35.191993 | | 3 | Mes
75 | 240 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 1 | Any
76 | 241 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 2 | Productes
77 | 242 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 3 | Mes
75 | 243 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 1 | Any
76 | 244 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 2 | Productes
77 | 245 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 3 | Mes
75 | 246 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 1 | Any
76 | 247 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 2 | Productes
77 | 248 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:31:35.191993 | 3 | Mes
75 | 249 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 1 | Any
76 | 250 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 2 | Productes
77 | 251 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 3 | Mes
76 | 252 | 2014-04-23 15:31:35.191993 | 2014-04-23 15:32:26.265934 | 4 | Productes
The model is ordered by sequence ASCENDING.
When searching with no _datetime set in context i get the following records (the order is important):
- Any (id:75)
- Mes (id:77)
- Productes (id:76)
Which is correct as it's using the model table, not the __history one.
When searching with a _datetime greater than last_write date (2014-04-23 15:32:26.265934), i get the following records:
- Any (id:75)
- Productes (id:76)
- Mes (id:77)
The record with id 76 is shown before of the record with id 77, which is not correct, as the correct value is the same when no _datetime in context.
I believe this is wrong because the record with __id = 250 it's returned before than the one with __id = 252 (which is the real value of the field), as the search query is ordered by sequence and the sequence of __id = 250 i less thant the sequence of the one with __id = 252.
I don't know if it's the reason of the bug is having a lot of duplicated data in the __history table, so I will explain how i managed to generate it:
__id < 249: This is a model which has a many2One with another model. So duplicating the "parent" model generates all those records. Three for the create call, and a group of 3 for each active language in the database (we have es_ES and ca_ES on the database, so 6 records more).
__id >= 249: We used D&D to change the sequence of one of the records (moving record with id 76 from second position to third position). When saving the parent record the client fires the following write:
{
'dimension': [
('add', [75, 76, 77]),
('write', [76], {'sequence': 4})
],
}
NOTE: dimension is the name of the One2Many field on the parent record.
Which causes the server to make two writes (one for add and another for write). The add one generates the records with __id between 249 and 251, and the write one generates the last record one.
We are having this issue on 3.0 version, but i have write a test which reproduces it on trunk. It can be found on http://codereview.tryton.org/5241002/