Re: Pulling up more complicated subqueries

On Wed, May 17, 2017 at 11:08 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
> That's not a straight semi-join, but we could still turn it into a new kind
> of LEFT-SEMI join. A left-semi join is like a left join, in that it returns
> all rows from the left side, and NULLs for any non-matches. And like a
> semi-join, it returns only one matching row from the right-side, if there
> are duplicates. In the qual, replace the SubLink with an IS NOT NULL test.
...
> This can be implemented using yet another new join type, a LEFT-UNIQUE join.
> It's like a LEFT JOIN, but it must check that there are no duplicates in the
> right-hand-side, and throw an error if there are (ERROR: more than one row
> returned by a subquery used as an expression).

It seems like we might want to split what is currently called JoinType
into two separate things -- one that is INNER/LEFT/RIGHT/FULL and the
other that says what to do about multiple matches, which could be that
they are expected, they are to be ignored (as in your LEFT-SEMI case),
or they should error out (as in your LEFT-UNIQUE case).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company