bug: CompileError: Multiple, unrelated CTEs found

[NickCrews]

What happened?

EDIT: see below I found a much simpler repro

Ok, sorry I'm not sure what the exact cause of this is. Thanks for the help!

Basically, I

1. load some data
2. perform some cleaning/transformations to get featured table
3. self join featured on condition A to get ja.
4. self join featured on condition B to get jb.
5. ibis.union(ja, jb)

If I do NOT .cache featured before the joins, then I get the error during the union. If I DO .cache featured before the joins, then there is no error. Regardless of whether I .cache(), I can always .execute() ja and jb before the union and it does not error.

Exact repro steps :

1. run git clone https://github.com/nickcrews/mismo.git and CD into that repo
2. git checkout 3105eaf7ef84ad5b629d7ce1177a05dc83469b68
3. Make a venv and install frozen deps. Install PDM if you don't have it. Then run pdm install.
4. open /docs/examples/patent_deduplication.ipynb
5. Run until you reach the cell containing rules.block(featured, featured)
6. If you comment out the .cache() call, you get the error. Try again with the .cache() and no error.

Let me know how else I can be helpful with debugging this.

What version of ibis are you using?

7.0.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

[cpcloud]

Thanks for opening the issue.

Would you mind coming up with a reproducer that can be run with only ibis installed and that doesn't require use of a library that depends on ibis?

[NickCrews]

yeah makes sense. Trying to minimize the changes I need to make: Are you concerned with some/all of

1. The other libs in the env interfering? the code at hand only uses ibis, I think if you just pip install ibis then I think it should work, but can double check if that is the case. I can try to reduce this if so.
2. you don't want to have to sift through the utils that load and featurize the data? This might be harder for me to workaround than...
3. you don't want to have to sift through the join/union logic? I think I can simplify this the easiest

Thanks!

[cpcloud]

Concerned with all of those 😄

How about trying to reproduce without loading and featurizing and trying only steps 3, 4 and 5 in the issue description?

[NickCrews]

yes I will do that!

[NickCrews]

OK so this is actually quite simple 🤣

import ibis

t = ibis.examples.penguins.fetch()
# No error if you remove the .head() call
t = t.head(100)

# The rename is simply needed so the schemas are the same so the union works
sub1 = t.inner_join(t.view(), "island").mutate(island_right=_.island)
sub2 = t.inner_join(t.view(), "sex").mutate(sex_right=_.sex)
u = ibis.union(sub1, sub2)
u

[cpcloud]

Thanks @NickCrews! I can reproduce.

This may not make the 7.1 cut, but it'll definitely be in the release following 7.1.

[cpcloud]

I'll check #7580 for this and add a test there if it's fixed.

[cpcloud]

Turns out this is also fixed by #7580.

[gforsyth]

Closing this out as fixed and tested on the-epic-split