feat: combine scalar from one table with column from other table

[NickCrews]

Is your feature request related to a problem?

I'm not sure if this is even possible with SQL, I was playing around with duckdb and I couldn't get it to work, but it seems like it should be possible (ie "well-defined") to me, so I was guessing I just didn't find the right SQL. If this isn't possible in SQL then feel free to close this.

import ibis

t1 = ibis.examples.penguins.fetch()
t2 = ibis.examples.penguins.fetch()
t1.body_mass_g + t2.body_mass_g.max()
# gives `RelationError: Cannot convert  expression involving multiple base table references to a projection`

Describe the solution you'd like

The above should work.

What version of ibis are you running?

6.0.0

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

duckdb

Code of Conduct

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

[cpcloud]

It's possible, using CROSS JOIN:

with bm_max as (select max(body_mass_g) as bmx from other_penguins)
select t.body_mass_g + bmx from penguins t cross join bm_max

[NickCrews]

Ah, I see, the problem was that I needed to do the max() inside the CTE, thank you!

So, can we make Ibis clever enough to do this for us?

[cpcloud]

I realized there's another way that doesn't involve an explicit cross join:

select body_mass_g + (select max(body_mass_g) from other_penguins)
from penguins

☝🏻 might be easier to give sane behavior to than cross join rewrites.

[lostmygithubaccount]

going to close as complete based on the discussion, feel free to re-open

[NickCrews]

related to #7655.

Yes this is good to close, the code snippet I posted now works on master! Thanks!