pepijnve opened a new issue, #27209:
URL: https://github.com/apache/datafusion/issues/27209
### Is your feature request related to a problem or challenge?
Table formats such as Delta Lake, Iceberg and Paimon can retains multiple
versions of a
table. These can be queried via SQL using `AS OF` syntax :
```sql
SELECT * FROM t VERSION AS OF 3;
SELECT * FROM t TIMESTAMP AS OF '2024-01-01';
```
`sqlparser` parses these clauses for several dialects (`VERSION AS OF`,
`TIMESTAMP AS OF`, `FOR SYSTEM_TIME AS OF`, `AT (...)`), but DataFusion has
no
way to pass the requested version to a catalog:
- The default planner silently ignores the clause. With the Databricks,
Snowflake, BigQuery or MsSQL dialects, `SELECT * FROM t VERSION AS OF 3`
returns the *current* version of `t` (see also #24754).
- `SchemaProvider::table` only takes a table name, so a catalog cannot be
asked for a specific version.
- The only workaround is a `RelationPlanner` extension, as Paimon uses. It
intercepts the clause, looks up the current table and builds a scan of the
requested snapshot itself. This approach works, but has a correctness
problem due to `TableScan` equality and hashing ignoring the table source.
Scans of different versions of the same table compare equal. Since a subquery's
plan is part of its expression's equality, the optimizer can merge subqueries
over
different versions:
```sql
SELECT
(SELECT count(*) FROM t AT (VERSION => 1)) AS v1,
(SELECT count(*) FROM t AT (VERSION => 2)) AS v2;
-- expected: 1, 2
-- with versions not taken into account in TableScan equality: 1, 1
```
### Describe the solution you'd like
First-class time travel support in the SQL planner and catalog API:
- Normalize the time travel syntaxes into a `TableVersion` which can be
either a scalar value version identifier or a timestamp.
- Evaluate the version expression during planning, so catalogs only see
constants. `AT (TIMESTAMP => now() - INTERVAL '1 hour')` works; column
references do not.
- Add `SchemaProvider::table_at(name, &TableVersion)`, returning that
version of the table. The default returns a "not implemented" error rather
than the current version.
- Let tables report the version they read (`TableProvider::version()` /
`TableSource::version()`). The resolved version may differ from the
requested
one: a timestamp or a tag typically resolves to a snapshot id.
- Include the resolved version in `TableScan` equality and hashing. This
fixes the
subquery problem above, and show it in `EXPLAIN`, e.g.
`TableScan: t@3 projection=[id, name]`.
- Keep existing `RelationPlanner` extensions working: they still run first
and can keep handling time travel clauses themselves. This avoids breaking
Paimon's current implementation.
### Describe alternatives you've considered
- Adding the version to `TableReference`, similar to how DuckDB passes it
with the table lookup. `TableReference` is also the column qualifier in
DataFusion, so `SELECT t.a FROM t VERSION AS OF 3` would stop resolving
`t.a`
unless the version were stripped everywhere a qualifier is created.
- Storing the version on `TableScan`. This grows `LogicalPlan`, and the
version is easily lost when a scan is rebuilt from its source. Keeping it
with
the source avoids both.
- Leaving time travel to `RelationPlanner` extensions. As described above,
extensions cannot make DataFusion distinguish scans of different versions.
- Encoding the version in the table name (e.g. BigQuery-style
`t@1234`, discussed in #7292). This changes column qualifiers and is hard
to
use with computed versions.
### Additional context
- Earlier discussion: #7292.
- The default `GenericDialect` does not parse time travel clauses yet, and
sqlparser only accepts number literals after `VERSION AS OF`. Both need
sqlparser changes, which I will propose upstream. `AT`/`BEFORE` also need a
guard there so they don't break unaliased table names like `FROM t at`.
- Prepared statement parameters in time travel clauses (`VERSION AS OF $1`)
are
out of scope: parameters are bound after planning, while the version
determines the table (and potentially its schema) during planning.
### Willingness to contribute
I plan to contribute to this feature independently. I have a working
implementation and will open a PR referencing this issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
