BUG: Fix duplicates in intersection of multiindexes
State: closed

- [x] closes #36915
- [x] xref #31326 (closes the intersection part)
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

Seems like this was not introduced on purpose. Probably introduced in #31312

Labels:
name: Regression
description: Functionality that used to work in a prior pandas version
name: MultiIndex

Comments:
Looking at CI possibly related so actually not sure this is good as is
You are right, this definitely is related. The problem is in 

https://github.com/pandas-dev/pandas/blob/653f6944eba664d19e8d93e850340ac039ec452e/pandas/core/ops/__init__.py#L482:L486

I think this should be ``left.columns.unique()`` and ``right.columns.unique()`` if the intersection should be unique?

cc @jbrockmendel 
With the change introduced by f4dc9f9028a3da539126f9a8a37e7c41fc7b4b3c we have to handle both issues at once to avoid bugs at other places
Had to change a check in merge, which relied on the wrong behavior of ``intersection``.
After taking another look, im thinking we might want to just disallow set ops when there are duplicates
@jbrockmendel You mean always returning ``False`` when left or right contains duplicates? In this case i have changed it accordingly
> You mean always returning False when left or right contains duplicates? In this case i have changed it accordingly

I mean that set operations only make sense when dealing with something set-like, i.e. unique.  So we could just raise `ValueError("Set operations are not well-defined on non-unique Indexes")`.  Not sure if thats desirable, just thinking out loud.
If we do this, we should probably raise ``DeprecationWarning`` first to avoid breaking code?
On further thought, if we were to disallow set operations with duplicates, Im not sure if there's a good "do X instead" we could offer users.  so probably ought to push forward with the approach here.
Merged master and rolled back to my initial fix then
this might be a regression but going to be work to backport. @phofl see if you can get all passing on master.
Failing tests relied on the wrong behavior, adjusted them
@phofl @jreback all comments now addressed?
One open point is what we should do with the unique check, because is_unique may not exist. This is only relevant to performance 
lgtm. small point about perf above (but i don't think it matters to backport, can just do on master).
thanks @phofl 
@meeseeksdev backport 1.1.x
Owee, I'm MrMeeseeks, Look at me.

There seem to be a conflict, please backport manually. Here are approximate instructions:

1. Checkout backport branch and update it.

```
$ git checkout 1.1.x
$ git pull
```

2. Cherry pick the first parent branch of the this PR on top of the older branch:
```
$ git cherry-pick -m1 e99e5ab32c4e831e7bbac0346189f4d6d86a6225
```

3. You will likely have some merge/cherry-pick conflict here, fix them and commit:

```
$ git commit -am 'Backport PR #36927: BUG: Fix duplicates in intersection of multiindexes'
```

4. Push to a named branch :

```
git push YOURFORK 1.1.x:auto-backport-of-pr-36927-on-1.1.x
```

5. Create a PR against branch 1.1.x, I would have named this PR:

> "Backport PR #36927 on branch 1.1.x"

And apply the correct labels and milestones.

Congratulation you did some good work ! Hopefully your backport PR will be tested by the continuous integration and merged soon!

If these instruction are inaccurate, feel free to [suggest an improvement](https://github.com/MeeseeksBox/MeeseeksDev).