Subscriptions on timestamps should use millisecond precision

[sync-by-unito]

There is an oddity at play in that MongoDB has millisecond precision and Realm uses nanosecond precision. This means that when we receive a query from Realm on a timestamp that has non-zero nanoseconds we can't safely convert that into a MongoDB date time query in such a way that we could guarantee the same results we send is what a local realm query would return.

I am not sure what the best route forward is, but I wanted to file a ticket to have it exist here in the hopes we can align on this at some point. This could include:

1. Only storing millisecond prevision
2. Only allowing queryies on millisecond precision
3. Anything else we think of

Error we generate:

encountered session error in QUERY message ||| unsupported query for table [STRING]: invalid timestamp query: can't use the '<=' operator with a timestamp value with precision finer than one millisecond.  Truncate the timestamp to millisecond precision or change the query to use '<' or '>' instead.

Relevant Link: https://github.com/10gen/baas/blob/b5d4ba5013c044a9da4fc4358ecd894d0cc50b6e/realm/rql/mql_convert.go#L502

[sync-by-unito]

➤ On 2022-10-28, Nicola Cabiddu commented:

[~jonathan.reams@mongodb.com] assigning this to you.

[sync-by-unito]

➤ On 2022-10-28, Jonathan Reams commented:

By the time a timestamp is being stored in a subscription, the query that created the timestamp has already been created with whatever precision it has. I think the server should just choose a rounding mode to the next millisecond and use that. If we do the rounding so the server sends more data than the exact local query would match then the user will be able to query more precisely on it locally anyways.

[sync-by-unito]

➤ On 2022-10-28, Michael O'Brien commented:

So, the server currently does choose a rounding mode depending on the operator in cases where it can do so in a way that guarantees that the result set will not diverge from the client's view.

Here's a summary of the current server behavior:

1. If the query from the device is doing a comparison on a timestamp that is a whole multiple of milliseconds (i.e. does not have any non-zero microsecond or nanosecond component, like 12:00:00+00+10.0ms), then the query can be executed exactly as-is, and should match the exact same result set that evaluating the same query on the local realm would.
2. If the query clause uses the exclusive range operator "<" and ">", the timestamp in the client's query is rounded down or up, respectively, to the next whole millisecond, and executed. This should also guarantee that the server's and client's result sets match exactly.
3. If the query clause uses any of the inclusive range operators ">=" "<=" or equality/inequality operators "==" and "!=", then the server rejects the query with an error, because it can't satisfy the query in a way that would produce stable results. Because the server does not store any timestamps with precision finer than 1 millisecond, then by definition, any query for an equality on a timestamp that does have precision finer than 1 millisecond can't have any results.

So, (1) and (2) already guarantee correct results without the user needing to modify their query in any way for many of the most common use cases. Number (3) is the case where we could try to improve the behavior by intercepting this on the client - either throw an error if the timestamp in the query has sub-millisecond precision (essentially, ask the user to tweak the query so that the server can handle it under case (1) above) or do the rounding for them.

Addressing (3) by doing server-side rounding seems like it could introduce odd behavior - because it means the server would be returning a result set that wouldn't actually match what a client would get if it evaluated the same query locally. That might have implications for things like client reset recovery, but in general seems like something we should be careful to avoid.

[sync-by-unito]

➤ On 2022-10-28, Jonathan Reams commented:

[~ian.ward@mongodb.com], should we just not have nanosecond-precision timestamps in realm? Or should you just not be able to query on them?

[sync-by-unito]

➤ On 2022-10-28, Michael O'Brien commented:

There's another possible quirky behavior related to this where a client might see a different value for a timestamp field depending on whether it received it from a bootstrap (i.e. pulled from the state store in mongo, in which its represented as a Date object with millisecond precision) or a history entry routed to the client during steady-state (a realm timestamp value that hasn't been roundtripped through the mongo Date representation, so it still has full nanosecond precision).

I think in an ideal world, the realm timestamp data type would have the same precision as mongo's; a user who wants to store nanosecond precision timestamps could always still represent them as an integer, if that's desired.

[sync-by-unito]

➤ On 2022-10-31, Ian Ward commented:

Yeah I don't think we change MongoDB's server storage right now - so we should just align with MongoDB. Would this be a breaking change? I thought this was already settled though. Don't we just drop the nanosecond precision or something?

[sync-by-unito]

➤ On 2022-10-31, Jonathan Reams commented:

Yeah, the problem is that you can construct a Query at nanosecond resolution which won't match anything on the server. I think we can make it so that if Sync is enabled the client truncates precision to milliseconds.

[sync-by-unito]

➤ jedelbo commented:

If we change realm to only support millisecond resolution, then I guess we can store the timestamp in a 64bit integer only. That would simplify a lot of things, but would of course be a breaking change.

[sync-by-unito]

➤ Jonathan Reams commented:

Added discuss label so we can find a path forward here. Fixing this so realm timestamps match the precision of mongo timestamps is a big task, but we should do something here so that querying on timestamps works correctly. Maybe we can roll this into next-major?

[sync-by-unito]

➤ Michael O'Brien commented:

I like the idea of keeping the data format the same, but just truncating it to millisecond precision. This might still qualify as a breaking change for certain behaviors? but at least its equivalent from a file format POV