WL#1604: Support FULL [OUTER] JOIN by rewriting with UNION

Affects: Prototype Only   —   Status: Un-Assigned   —   Priority: Medium

SELECT ... FROM a FULL [OUTER] JOIN b ON a.id=b.id

is equivalent to

(SELECT ... FROM a LEFT JOIN b ON a.id=b.id)
UNION
(SELECT ... FROM a RIGHT JOIN b ON a.id=b.id)

(the UNION without ALL takes care of eliminating the dups caused by rows
actually matching the join condition)

[As PeterG noted:

It might do more than that.
 
mysql> create table t1 (s1 int);
mysql> create table t2 (s1 int);
mysql> insert into t1 values (1);
mysql> insert into t1 values (1);
mysql> select * from t1 left join t2 on (t1.s1=t2.s1);
+------+------+
| s1   | s1   |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on (t1.s1=t2.s1)
 union select * from t1 right join t2 on (t1.s1=t2.s1);
+------+------+
| s1   | s1   |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

So the "full join" returns fewer rows than the left join.]

As a full outer join can't be optimized much anyway, the above appears to be a
pretty decent solution.

If the joined fields are indexed, we could utilize a method similar to our new
"merge index" optimisation to intersect the two indexes and optimize the
operation that way.

Feature requests:
BUG#18003 FULL OUTER JOIN (no syntax to cover)