bug: surprising semantics for ibis.random() expressions

[ogrisel]

What happened?

I am not sure if this is a bug or not, but if it is not, at least the docstring for ibis.random() could clarify the expected semantics.

Consider the following:

>>> import ibis
>>> ibis.options.interactive = True

>>> t = ibis.memtable({"a": range(10)})
>>> r_a = ibis.random()
>>> t.mutate(random_a=r_a, random_b=r_a).mutate(random_c=ibis._.random_a).order_by(r_a)
┏━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┓
┃ a     ┃ random_a ┃ random_b ┃ random_c ┃
┡━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━┩
│ int64 │ float64  │ float64  │ float64  │
├───────┼──────────┼──────────┼──────────┤
│     1 │ 0.238328 │ 0.479795 │ 0.238328 │
│     3 │ 0.951708 │ 0.982469 │ 0.951708 │
│     9 │ 0.923080 │ 0.638801 │ 0.923080 │
│     8 │ 0.338851 │ 0.270337 │ 0.338851 │
│     5 │ 0.469603 │ 0.418677 │ 0.469603 │
│     7 │ 0.247954 │ 0.043970 │ 0.247954 │
│     0 │ 0.558676 │ 0.566343 │ 0.558676 │
│     6 │ 0.276563 │ 0.220646 │ 0.276563 │
│     2 │ 0.068436 │ 0.045429 │ 0.068436 │
│     4 │ 0.360141 │ 0.846183 │ 0.360141 │
└───────┴──────────┴──────────┴──────────┘

What I find surprising:

• despite the reuse of the r_a instance for both random_a and random_b columns, their values are not the same;
• similarly, despite the reuse of the r_a instance to order the table, the random_a column values are not sorted in increasing order.

random_c on the other hand has identical values with random_a as expected.

I would have expected the above results to happen with independent instances of ibis.random(), that is with the following snippet where all column values and ordering as explicitly made independent:

t = ibis.memtable({"a": range(10)})
t.mutate(random_a=ibis.random(), random_b=ibis.random()).mutate(
    random_c=ibis._.random_a
).order_by(ibis.random())

Related: #8054.

What version of ibis are you using?

7.2.0

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

DuckDB

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[NickCrews]

Not sure about what we want or is possible (wait for a maintainer to chime in), but I have the explanation: this has to do with the semantics of the underlying duckdb:

import ibis
ibis.options.interactive = True
t = ibis.memtable({"a": range(10)})
r_a = ibis.random()
t2 = t.mutate(random_a=r_a, random_b=r_a).mutate(random_c=ibis._.random_a).order_by(r_a)
ibis.to_sql(t2)

gives

WITH t0 AS (
  SELECT
    t2.a AS a,
    RANDOM() AS random_a,
    RANDOM() AS random_b
  FROM ibis_pandas_memtable_clvyqxmilvg3tnmhfadqgybpxi AS t2
)
SELECT
  t1.a,
  t1.random_a,
  t1.random_b,
  t1.random_c
FROM (
  SELECT
    t0.a AS a,
    t0.random_a AS random_a,
    t0.random_b AS random_b,
    t0.random_a AS random_c
  FROM t0
) AS t1
ORDER BY
  RANDOM() ASC

and every individual RANDOM() call is totally unrelated to the others.

I believe that you can always work around this limitation by .mutate()ing earlier/more often and then using the new columns, eg t.mutate(random_a=r_a).mutate( random_b=ibis._.random_a).mutate(random_c=ibis._.random_a).order_by(ibis._.random_a). I agree this is less beautiful than what you posted originally, but if you;re just trying to get something done...