@Tmonster here's what I was referring to

I see, so similar to Union, intersect all is intersect with duplicates and intersect is automatically de-duplicated. Same with except all and except.

I feel like this logic could also go in my PR. intersect all can be transformed into a semi join while intersect is turned into a semi join with a distinct pushed on top? Except is similar but using an anti join instead.

My PR isn't ready yet. I still need to use the column binding replacer when I replace the set op.

Also just a thought, if I add the optimizer to convert intersect/except set ops to semi/anti joins early, and we keep the automatic deduplication, the join order optimizer has less opportunities for optimizing around the semi/anti joins since the group by operator won't allow it

No idea what to do about the storage serialisation test failure

The failing test seems to be that this is breaking forwards compatibility (i.e. v0.9.2 will not be able to read databases made with v0.10.0 containing views with UNION ALL after this patch). Breaking forwards compatibility is generally fine so we can merge this in spite of the test failure IMO. @carlopi agreed?

Here some kind of breakage, either having 0.9.1 not be able to read all 0.9.2+ files or 0.9.2 not be able to read all 0.10.0 files is probably the way to go.
We are generally fine with breaking forward, unsure whether it's best to do it now or wait post 0.9.x.

Unsure on what's worse, between changing behaviour between minor versions or live a couple of more months with what we know have decided is a wrong behaviour.

The only middle way that I could think of, but it's an hassle long term, is having a setting like intersect_all_compatilibity, default that to true for 0.9.2, and later switch it to false. That would make 0.9.2 be able to interact with both 0.9.1 (forward and backward) and 0.10.x (forward and backward) depending on the status of the flag.

I would say this is a feature and shouldn't go in 0.9.2, then we can break forwards compatibility between 0.9.2 and 0.10.0 on views with union/intersect/except

Thanks!

This PR breaks R tests, but I don't see a corresponding CI/CD run here. Are we still running R tests in this repository?

This PR breaks R tests, but I don't see a corresponding CI/CD run here. Are we still running R tests in this repository?

We aren't running the R tests in this repository. Just checking the build. I'll investigate the failing test this week

I'm on it, let's discuss later today.

Have a PR to fix the R tests here duckdb/duckdb-r#54