> > A JOIN B for SYSTEM_TIME AS OF A.PROC_TIME > This sounds like a solution, but it has issues: - The `B` table will have to contain an application-defined `proc_time` column. What if that time is from the future? And even if it's not, what if the record in B is late? A and B tables can be managed by different brokers (for example a Kafka topic A and a PostgreSQL table B). I'm not sure but I guess the `FOR SYSTEM_TIME AS OF` query fails if the time is a future instant. - Maybe you've meant to use `AS OF CURRENT_TIMESTAMP` instead of `A.proc_time`. But normally the CURRENT_TIMESTAMP is constant during the query execution which turns the table into an immutable one. If CURRENT_TIMESTAMP has different value depending on the evaluation time, then the table actually changes and the query should stream the changes. Specifying how it should behave seems very tricky to me and also implementation dependent - it depends on the type of access and of join. - Also system-versioned temporal tables bring overhead. But we could eliminate that by saying "this temporal table can only be queried as of CURRENT_TIMESTAMP". But as-of-current-timestamp is the default for temporal tables when the as-of clause isn't specified, which is supposed to make them compatible with normal tables, so it's not a temporal table at all. And we're back at the original issue. So I don't know, there's a missing piece here :).
Anyway, it seems tricky to reason this syntax. And in SQL it's dangerous to try a query and check the results, I've seen people removing ORDER BY because it was faster and results in their experiments were ordered anyway. See https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf Viliam -- This message contains confidential information and is intended only for the individuals named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version. -Hazelcast
