Is there an `ANY` or `ANY_VALUE` expression in Ibis that will let me compare a value from a set of values returned by a subquery?

Question

Let t = ibis.memtable({'col0': [-2, -5, -1, 0, 1, 8, 7], 'col1': [0, -2, 4, 5, 6, 8, 7]}). I want to find all of the rows where col0 is any value in col1.

In SQL, I might do:

SELECT
  *
FROM
  t
WHERE
  col0 = ANY(SELECT col1 FROM t)

(or ANY_VALUE)

Is there a column expression or ibis method for ANY or ANY_VALUE? or a workaround?

I tried looking in the docs but did not come up with an answer.

Answer 1

You can express this kind of computation using our somewhat specialized version of .any() along with view() to make sure that the column you're comparing against is scanned in full for every value of the search column:

In [17]: from ibis.interactive import *

In [18]: t = ibis.memtable({"a": [1, 2, 3, 4, 5, 6], "b": [3, 4, 5, 6, 1, 2]})

In [19]: t[(t.a > t.view().b).any()]
Out[19]:
┏━━━━━━━┳━━━━━━━┓
┃ a     ┃ b     ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     5 │     1 │
│     2 │     4 │
│     4 │     6 │
│     3 │     5 │
│     6 │     2 │
└───────┴───────┘