On Wed, May 17, 2017 at 11:08 AM, Heikki Linnakangas <hlinn...@iki.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
