Error querying VALID_TIME with timestamp arithmetic

Users
1

Hi,

I’m playing around with the SQL syntax for XTDB 2 and I’m getting an error when I try to query VALID_TIME like this:

SELECT docs.xt$id, docs.foo FROM docs FOR VALID_TIME AS OF (CURRENT_TIMESTAMP - INTERVAL 'PT1M');

Here’s a fiddle where I was trying it out:

https://fiddle.xtdb.com/?version=2.0.0-SNAPSHOT&type=sql&txs=W3sic3lzdGVtLXRpbWUiOm51bGwsInR4cyI6IklOU0VSVCBJTlRPIGRvY3MgKHh0JGlkLCBmb28pIFZBTFVFUyAoMSwgJ2JhcicpIn1d&query=U0VMRUNUIGRvY3MueHQkaWQsIGRvY3MuZm9vIEZST00gZG9jcyBGT1IgVkFMSURfVElNRSBBUyBPRiAoQ1VSUkVOVF9USU1FU1RBTVAgLSBJTlRFUlZBTCAnUFQxTScpOwo%3D

Here’s the error I get:

Error: xtdb.IllegalArgumentException
Illegal argument: 'malformed-query'
Data:
{:plan ["scan" {:table "docs", :for-valid-time ["at" ["-" ["current-timestamp"] {:period "P0D", :duration 60}]]} ["xt$id" "foo"]], :explain {:clojure.spec.alpha/problems [{:path ["scan" "scan-opts" "for-valid-time" "clojure.spec.alpha/nil"], :pred "nil?", :val ["at" ["-" ["current-timestamp"] {:period "P0D", :duration 60}]], :via ["xtdb.logical-plan/logical-plan" "xtdb.logical-plan/for-valid-time"], :in [1 "for-valid-time"]} {:path ["scan" "scan-opts" "for-valid-time" "clojure.spec.alpha/pred" "at" 1 "now"], :pred ["now" ["quote" ["current-timestamp"]]], :val ["-" ["current-timestamp"] {:period "P0D", :duration 60}], :via ["xtdb.logical-plan/logical-plan" "xtdb.logical-plan/for-valid-time" "xtdb.logical-plan/temporal-filter" "xtdb.logical-plan/temporal-filter-value"], :in [1 "for-valid-time" 1]} {:path ["scan" "scan-opts" "for-valid-time" "clojure.spec.alpha/pred" "at" 1 "literal"], :pred ["clojure.core/some-fn" "xtdb.logical-plan/util-date?" "xtdb.logical-plan/temporal?"], :val ["-" ["current-timestamp"] {:period "P0D", :duration 60}], :via ["xtdb.logical-plan/logical-plan" "xtdb.logical-plan/for-valid-time" "xtdb.logical-plan/temporal-filter" "xtdb.logical-plan/temporal-filter-value"], :in [1 "for-valid-time" 1]} {:path ["scan" "scan-opts" "for-valid-time" "clojure.spec.alpha/pred" "at" 1 "param"], :pred "clojure.core/simple-symbol?", :val ["-" ["current-timestamp"] {:period "P0D", :duration 60}], :via ["xtdb.logical-plan/logical-plan" "xtdb.logical-plan/for-valid-time" "xtdb.logical-plan/temporal-filter" "xtdb.logical-plan/temporal-filter-value"], :in [1 "for-valid-time" 1]}], :clojure.spec.alpha/spec "xtdb.logical-plan/logical-plan", :clojure.spec.alpha/value ["scan" {:table "docs", :for-valid-time ["at" ["-" ["current-timestamp"] {:period "P0D", :duration 60}]]} ["xt$id" "foo"]]}, :xtdb.error/error-key "malformed-query"}

Any ideas?

2

Hey @caleb_land thanks for raising this. Unfortunately we don’t currently support expressions for the AS OF value, and the error message isn’t as clear as it should be - I will update the thread with an issue on Monday where you can track the progress (it is quite likely we will add support for arbitrary expressions). In the meantime though you should at least be able to specify the value using a query parameter.

Hope that helps!

UPDATE: here’s the issue Support for expressions in temporal filters · Issue #3306 · xtdb/xtdb · GitHub

1 Like