I’m reviewing the temporal query support in HoneySQL and I’ve noticed one bug and one thing I’m not sure of.
The bug is that HoneySQL currently generates FROM table AS alias FOR ... whereas it looks like this should be FROM table FOR ... AS alias. That seems a pretty clear bug.
The other issue is that HoneySQL generates FROM table FOR SYSTEM_TIME AS OF ... or FROM table FOR SYSTEM_TIME BETWEEN .. AND .. or FROM table FOR SYSTEM_TIME FROM .. TO .. – but also FROM table FOR SYSTEM_TIME ALL.
Looking at the documentation for SQL Server, that’s what it expects.
Looking at the documentation for XTDB, it seems to expect FROM table FOR ALL SYSTEM_TIME.
Not all databases that support temporal queries seem to support ALL.
Is this a SQL:2011 standard or is ALL an extension? Why is there a difference between XTDB’s SQL and SQL Server’s?
The bug is that HoneySQL currently generates FROM table AS alias FOR ... whereas it looks like this should be FROM table FOR ... AS alias. That seems a pretty clear bug.
Yep, checked against the SQL standard too. FWIW, FOR VALID_TIME ... is also technically an extension of the standard here - the standard is to use separate period predicates for valid-time filtering. We also deliberately deviate from the standard in that we default to current-time queries in valid-time as well as system-time (SQL’s default is current system-time, all valid-time) - we have a flag to restore the standards-compliant behaviour but we feel it’s more likely people will explicitly use ‘for all valid_time’ if they want that behaviour.
Speaking of which 
:
Not all databases that support temporal queries seem to support ALL.
Is this a SQL:2011 standard or is ALL an extension? Why is there a difference between XTDB’s SQL and SQL Server’s?
Yes, this is also an extension of the standard. Openly, it’s an oversight on our part to have differed here - IMO, FOR ALL SYSTEM_TIME is more in keeping with the other filters (in terms of reading naturally) but there’s probably more value in bringing ourselves in line with SQL Server.
edit: Consider `FOR SYSTEM_TIME ALL` for alignment with SQL Server · Issue #3072 · xtdb/xtdb · GitHub
I ran across FOR BUSINESS_TIME as supported syntax in one database, when trying to research the syntax.
Re: FOR SYSTEM_TIME ALL (and FOR VALID_TIME ALL) – thank you. I won’t take action on that with HoneySQL until y’all confirm whether you’re making this change (since SQL Server is pretty common and I definitely have to support that).
1 Like
Yeah - the SQL standard doesn’t ever specify a name/reserved keyword for the application time period (unlike SYSTEM_TIME), it’s up to the user to name the period as part of CREATE TABLE. We don’t have create-table, so we chose valid-time, in keeping with XT1 and the original bitemporal literature, but I’ve seen it called business-time, application-time, effective-time, domain-time etc etc etc 