BUG: left join on index with multiple matches now works (GH5391)

    >>> ### left join with multiple matches - single index case

    >>> left = DataFrame([
    ...     ['X', 'Y', 'C', 'a'],
    ...     ['W', 'Y', 'C', 'e'],
    ...     ['V', 'Q', 'A', 'h'],
    ...     ['V', 'R', 'D', 'i'],
    ...     ['X', 'Y', 'D', 'b'],
    ...     ['X', 'Y', 'A', 'c'],
    ...     ['W', 'Q', 'B', 'f'],
    ...     ['W', 'R', 'C', 'g'],
    ...     ['V', 'Y', 'C', 'j'],
    ...     ['X', 'Y', 'B', 'd']],
    ...     columns=['cola', 'colb', 'colc', 'tag'],
    ...     index=[3, 2, 0, 1, 7, 6, 4, 5, 9, 8])
    >>>                                                                    
    ... right = DataFrame([
    ...     ['W', 'R', 'C',  0],
    ...     ['W', 'Q', 'B',  3],
    ...     ['W', 'Q', 'B',  8],
    ...     ['X', 'Y', 'A',  1],
    ...     ['X', 'Y', 'A',  4],
    ...     ['X', 'Y', 'B',  5],
    ...     ['X', 'Y', 'C',  6],
    ...     ['X', 'Y', 'C',  9],
    ...     ['X', 'Q', 'C', -6],
    ...     ['X', 'R', 'C', -9],
    ...     ['V', 'Y', 'C',  7],
    ...     ['V', 'R', 'D',  2],
    ...     ['V', 'R', 'D', -1],
    ...     ['V', 'Q', 'A', -3]],
    ...     columns=['col1', 'col2', 'col3', 'val'])
    >>>                                                                    
    ... right.set_index(['col1', 'col2', 'col3'], inplace=True)
    >>> result = left.join(right, on=['cola', 'colb', 'colc'], how='left')
    >>>                                                                    
    ... expected = DataFrame([
    ...     ['X', 'Y', 'C', 'a',   6],
    ...     ['X', 'Y', 'C', 'a',   9],
    ...     ['W', 'Y', 'C', 'e', nan],
    ...     ['V', 'Q', 'A', 'h',  -3],
    ...     ['V', 'R', 'D', 'i',   2],
    ...     ['V', 'R', 'D', 'i',  -1],
    ...     ['X', 'Y', 'D', 'b', nan],
    ...     ['X', 'Y', 'A', 'c',   1],
    ...     ['X', 'Y', 'A', 'c',   4],
    ...     ['W', 'Q', 'B', 'f',   3],
    ...     ['W', 'Q', 'B', 'f',   8],
    ...     ['W', 'R', 'C', 'g',   0],
    ...     ['V', 'Y', 'C', 'j',   7],
    ...     ['X', 'Y', 'B', 'd',   5]],
    ...     columns=['cola', 'colb', 'colc', 'tag', 'val'],
    ...     index=[3, 3, 2, 0, 1, 1, 7, 6, 6, 4, 4, 5, 9, 8])
    >>>                                                                    
    ... tm.assert_frame_equal(result, expected)
    >>> print(left, right, result, sep='\n')

      cola colb colc tag
    3    X    Y    C   a
    2    W    Y    C   e
    0    V    Q    A   h
    1    V    R    D   i
    7    X    Y    D   b
    6    X    Y    A   c
    4    W    Q    B   f
    5    W    R    C   g
    9    V    Y    C   j
    8    X    Y    B   d

                    val
    col1 col2 col3     
    W    R    C       0
         Q    B       3
              B       8
    X    Y    A       1
              A       4
              B       5
              C       6
              C       9
         Q    C      -6
         R    C      -9
    V    Y    C       7
         R    D       2
              D      -1
         Q    A      -3

      cola colb colc tag  val
    3    X    Y    C   a    6
    3    X    Y    C   a    9
    2    W    Y    C   e  NaN
    0    V    Q    A   h   -3
    1    V    R    D   i    2
    1    V    R    D   i   -1
    7    X    Y    D   b  NaN
    6    X    Y    A   c    1
    6    X    Y    A   c    4
    4    W    Q    B   f    3
    4    W    Q    B   f    8
    5    W    R    C   g    0
    9    V    Y    C   j    7
    8    X    Y    B   d    5
    
    >>> ### left join with multiple matches - multi index case

    >>> left = DataFrame([
    ...     ['c', 0],
    ...     ['b', 1],
    ...     ['a', 2],
    ...     ['b', 3]],
    ...     columns=['tag', 'val'],
    ...     index=[2, 0, 1, 3])
    >>>                                                 
    ... right = DataFrame([
    ...     ['a', 'v'],
    ...     ['c', 'w'],
    ...     ['c', 'x'],
    ...     ['d', 'y'],
    ...     ['a', 'z'],
    ...     ['c', 'r'],
    ...     ['e', 'q'],
    ...     ['c', 's']],
    ...     columns=['tag', 'char'])
    >>>                                                 
    ... right.set_index('tag', inplace=True)
    >>> result = left.join(right, on='tag', how='left')
    >>>                                                 
    ... expected = DataFrame([
    ...     ['c', 0, 'w'],
    ...     ['c', 0, 'x'],
    ...     ['c', 0, 'r'],
    ...     ['c', 0, 's'],
    ...     ['b', 1, nan],
    ...     ['a', 2, 'v'],
    ...     ['a', 2, 'z'],
    ...     ['b', 3, nan]],
    ...     columns=['tag', 'val', 'char'],
    ...     index=[2, 2, 2, 2, 0, 1, 1, 3])
    >>>                                                 
    ... tm.assert_frame_equal(result, expected)
    >>> print(left, right, result, sep='\n')

      tag  val
    2   c    0
    0   b    1
    1   a    2
    3   b    3

        char
    tag     
    a      v
    c      w
    c      x
    d      y
    a      z
    c      r
    e      q
    c      s

      tag  val char
    2   c    0    w
    2   c    0    x
    2   c    0    r
    2   c    0    s
    0   b    1  NaN
    1   a    2    v
    1   a    2    z
    3   b    3  NaN

This closes on: https://github.com/pydata/pandas/issues/5391
By providing all the matches when doing left join on index, both in the case of single index and multi-index. It also preserves the index order of the calling (left) DataFrame (as it used to), though when there are multiple matches the indices repeat and the index loses integrity.

The added test cases should be self-explanatory.

Thank you,