Postgres error: DateDelta

jeffbrainerd commented 1 month ago

What happened?

Executing the following code,

import ibis
t = ibis.table(dict(one="date", two="date"), name="example")
tt = t.filter(t.one.delta(t.two, "year") > 10)
ibis.to_sql(tt, dialect="postgres")

I received an error: No translation rule for <class 'ibis.expr.operations.temporal.DateDelta'>

I expected this to work for Postgres based on the Support Matrix for DateDelta. The code works fine for DuckDB (the only other backend I tried).

I definitely appreciate any action or workarounds for this. Thank you!

What version of ibis are you using?

8.0.0

What backend(s) are you using, if any?

Postgres

Relevant log output

Traceback (most recent call last):
  File "/home/jeff/.pyenv/versions/enobase/lib/python3.9/site-packages/IPython/core/interactiveshell.py", line 3548, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-6-c80bae9e7d40>", line 1, in <module>
    ibis.to_sql(tt, dialect="postgres")
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/expr/sql.py", line 370, in to_sql
    sql = backend._to_sql(expr, **kwargs)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/__init__.py", line 196, in _to_sql
    sql = self.compile(expr, **kwargs).compile(
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/__init__.py", line 395, in compile
    return self.compiler.to_ast_ensure_limit(expr, limit, params=params).compile()
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/compiler/base.py", line 37, in compile
    compiled_queries = [q.compile() for q in self.queries]
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/compiler/base.py", line 37, in <listcomp>
    compiled_queries = [q.compile() for q in self.queries]
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/query_builder.py", line 224, in compile
    frag = step(frag)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/query_builder.py", line 334, in _add_where
    args = [
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/query_builder.py", line 335, in <listcomp>
    self._translate(pred, permit_subquery=True, within_where=True)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/compiler/query_builder.py", line 239, in _translate
    return translator.get_result()
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/compiler/translator.py", line 224, in get_result
    translated = self.translate(self.node)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/compiler/translator.py", line 256, in translate
    return formatter(self, op)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/registry.py", line 58, in formatter
    return _varargs_call(sa_func, t, op.args)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/registry.py", line 66, in _varargs_call
    arg = t.translate(raw_arg)
  File "/home/jeff/.pyenv/versions/3.9.15/envs/enobase/lib/python3.9/site-packages/ibis/backends/base/sql/compiler/translator.py", line 258, in translate
    raise com.OperationNotDefinedError(f"No translation rule for {type(op)}")
ibis.common.exceptions.OperationNotDefinedError: No translation rule for <class 'ibis.expr.operations.temporal.DateDelta'>

Code of Conduct

[X] I agree to follow this project's Code of Conduct

gforsyth commented 1 month ago

Hey @jeffbrainerd -- if you upgrade to Ibis 9.0, this is implemented (the operations matrix is usually reflective of our main branch or the latest release).

[ins] In [1]: import ibis
         ...: 
         ...: t = ibis.table(dict(one="date", two="date"), name="example")
         ...: tt = t.filter(t.one.delta(t.two, "year") > 10)
         ...: ibis.to_sql(tt, dialect="postgres")
Out[1]: 
SELECT
  *
FROM "example" AS "t0"
WHERE
  CAST(EXTRACT(year FROM AGE(CAST("t0"."one" AS TIMESTAMP), CAST("t0"."two" AS TIMESTAMP))) AS BIGINT) > 10

jeffbrainerd commented 1 month ago

That's good news, thank you @gforsyth .

ibis-project / ibis