`NATURAL FULL JOIN` incorrectly parsed as `NATURAL RIGHT JOIN`

[TheoristCoder]

Dear Dolt Developers,
I encountered an unexpected execution result in Dolt and would greatly appreciate it if you could take a look. Let's make concerted efforts in enhancing the robustness of Dolt. Thank you very much!

CREATE TABLE t0(c0 BOOLEAN, c1 INT, PRIMARY KEY(c0));
CREATE TABLE t1(c0 BOOLEAN, c1 VARCHAR(500), PRIMARY KEY(c0));
INSERT INTO t1(c1, c0) VALUES (NULL, true);
INSERT INTO t0(c0, c1) VALUES (true, 4);

SELECT * FROM t1 NATURAL FULL JOIN t0;
-- 1, 4

SELECT /*+ MERGE_JOIN(t0, t1)*/ * FROM t1 NATURAL FULL JOIN t0;
-- Empty Result

Commit: ed7ddc5

[angelamayxie]

@TheoristCoder can you tell us more about the DBMS you've been doing? You can get in touch with us via Discord https://discord.gg/ef5FG2NT

[angelamayxie]

It seems like both join queries are wrong here. The correct result should return both rows ({1, 4}, {1, null}).

The query plans look like this

tmp/main*> explain plan select * from t1 natural full join t0;
+-----------------------------------------------+
| plan                                          |
+-----------------------------------------------+
| Project                                       |
|  ├─ columns: [t0.c0, t0.c1]                   |
|  └─ LeftOuterJoin                             |
|      ├─ ((t0.c0 = t1.c0) AND (t0.c1 = t1.c1)) |
|      ├─ Table                                 |
|      │   ├─ name: t0                          |
|      │   └─ columns: [c0 c1]                  |
|      └─ Table                                 |
|          ├─ name: t1                          |
|          └─ columns: [c0 c1]                  |
+-----------------------------------------------+
10 rows in set (0.00 sec) 


tmp/main*> explain plan select /*+ MERGE_JOIN(t0, t1)*/ * from t1 natural full join t0;
+------------------------------------+
| plan                               |
+------------------------------------+
| Project                            |
|  ├─ columns: [t0.c0, t0.c1]        |
|  └─ LeftOuterMergeJoin             |
|      ├─ cmp: (t0.c0 = t1.c0)       |
|      ├─ sel: (t0.c1 = t1.c1)       |
|      ├─ IndexedTableAccess(t0)     |
|      │   ├─ index: [t0.c0]         |
|      │   ├─ filters: [{[NULL, ∞)}] |
|      │   └─ columns: [c0 c1]       |
|      └─ IndexedTableAccess(t1)     |
|          ├─ index: [t1.c0]         |
|          ├─ filters: [{[NULL, ∞)}] |
|          └─ columns: [c0 c1]       |
+------------------------------------+
13 rows in set (0.00 sec) 

A FULL NATURAL JOIN should not be getting optimized into a LeftOuterJoin. It doesn't seem like we currently have any testing for FULL NATURAL JOIN (only FULL JOIN and NATURAL JOIN) so I don't think it's something we currently support.

[angelamayxie]

Vitess currently parses NATURAL FULL JOIN as a NATURAL RIGHT JOIN, which is the root of the problem. I'll add it as a new join type, meaning it will no longer be handled in GMS/Dolt, and file a subsequent issue to implement it as a new feature.