title: LEFT OUTER JOIN on array column with multiple values
body: <p>I cannot seem to find the trick to join two tables through an array column when one table is not an array value, and the other table's array value can contain multiple values. It does work when there is a single valued array. </p>
  
  <p>Here's a simple minimal example of what I'm talking about. The real tables have GIN indexes on the array columns FWIW. These do not, but the query behaves the same.</p>
  
  <pre><code>DROP TABLE IF EXISTS eg_person;
  CREATE TABLE eg_person (id INT PRIMARY KEY, name TEXT);
  INSERT INTO eg_person (id, name) VALUES
    (1, 'alice')
  , (2, 'bob')
  , (3, 'charlie');
  
  DROP TABLE IF EXISTS eg_assoc;
  CREATE TABLE eg_assoc (aid INT PRIMARY KEY, actors INT[], benefactors INT[]);
  INSERT INTO eg_assoc (aid, actors, benefactors) VALUES
    (1, '{1}'  , '{2}')
  , (2, '{1,2}', '{3}')
  , (3, '{1}'  , '{2,3}')
  , (4, '{4}'  , '{1}');
  
  SELECT aid, actors, a_person.name, benefactors, b_person.name 
  FROM   eg_assoc
  LEFT   JOIN eg_person a_person on array[a_person.id] @&gt; eg_assoc.actors
  LEFT   JOIN eg_person b_person on array[b_person.id] @&gt; eg_assoc.benefactors;
  </code></pre>
  
  <p>The actual results are this like so. The issue here is that name column comes up <code>NULL</code> if either <code>actors</code> or <code>benefactors</code> contains more than one value.</p>
  
  <pre><code> aid | actors | name  | benefactors |  name   
  -----+--------+-------+-------------+---------
     1 | {1}    | alice | {2}         | bob
     2 | {1,2}  |       | {3}         | charlie
     3 | {1}    | alice | {2,3}       | 
     4 | {4}    |       | {1}         | alice
  </code></pre>
  
  <p>I was expecting this:</p>
  
  <pre><code> aid | actors | name  | benefactors |  name   
  -----+--------+-------+-------------+---------
     1 | {1}    | alice | {2}         | bob
     2 | {1,2}  | alice | {3}         | charlie
     2 | {1,2}  | bob   | {3}         | charlie
     3 | {1}    | alice | {2,3}       | bob
     3 | {1}    | alice | {2,3}       | charlie
     4 | {4}    |       | {1}         | alice
  </code></pre>
  
  <p>It would be really nice if I could get it to look like this, though:</p>
  
  <pre><code> aid | actors | name        | benefactors |  name   
  -----+--------+-------------+-------------+---------
     1 | {1}    | {alice}     | {2}         | {bob}
     2 | {1,2}  | {alice,bob} | {3}         | {charlie}
     3 | {1}    | {alice}     | {2,3}       | {bob, charlie}
     4 | {4}    |             | {1}         | {alice}
  </code></pre>
  
  <p>I'm aware that this schema denormalized, and I'm willing to go to a normal representation if need be. However, this is for a summary query and it already involves a lot more joins than I'd like.</p>