I just updated to the latest trunk check-in to try out full outer join on a query I had that used the workaround with two left join selects union'd together, and got properly bamboozled by the results I was getting for a bit.

If you use USING (Column) then Column seems to be whatever the left-hand side table's value of Column is, even if that's null and the right-hand side's value isn't. This is easily worked around by just using t1.Column and t2.Column, but it feels like the sort of thing that's unintentional. I tested Postgres and that did not exhibit this behaviour.

I suppose my question is: is this on purpose or just a result of full joins being extremely new and still in progress?

Example:

    CREATE TABLE t1 (ID INTEGER);
    CREATE TABLE t2 (ID INTEGER);
    INSERT INTO t1 VALUES (1);
    INSERT INTO t2 VALUES (2);
    SELECT * FROM t1 FULL JOIN t2 USING (ID);

    SQLite:
    ┌──────┐
    │  ID  │
    ├──────┤
    │ 1    │
    │ NULL │
    └──────┘

    Postgres:
     id
    ----
      1
      2

Looks to me like they haven't got around to dealing properly with the shared column in full joins yet. It's a bit easier to see what is going on with an extra column to mark the data:

CREATE TABLE t1 (ID INTEGER,t1);
    CREATE TABLE t2 (ID INTEGER,t2);
    INSERT INTO t1 VALUES (1,'x');
    INSERT INTO t2 VALUES (2,'x');
    SELECT * FROM t1 FULL JOIN t2 USING (ID);

with the result

┌────┬────┬────┐
│ ID │ t1 │ t2 │
├────┼────┼────┤
│ 1  │ x  │    │
│    │    │ x  │
└────┴────┴────┘

Most likely, that's the old code for left joins still in use for the shared column, since left joins used to be the only outer join available.

FULL is even not documented as valid JOIN syntax in fact.

Yet you didn't get an error, so is that a doc oversight?

Or someone FULL is silently ignored somehow? But then why?
FULL is documented as being a keyword though. Maybe that's why...

Sure. I had missed the fact this was on unreleased trunk, so my bad.
Then consider it just a reminder to update the railroad diagrams when this lands.

Thank you for the bug report, and for trying out the new feature prior to release!

I have clearly messed up the handling of USING (and by extension NATURAL) for RIGHT and LEFT JOIN. I'll work on the problem and post a follow-up if and when I have a solution.

I have independently found other problems with RIGHT/FULL JOIN over and above the USING problem described here. For that reason, the enhancement has been moved back onto its branch. Check-in fa9d206f904280e3 which was originally a merge from the "right-join" feature branch into trunk, has been converted into a merge of trunk into the right-join feature branch (a change which one can accomplish in Fossil simply by adding a tag to the check-in in question - no refactoring or history rewriting required).

I will continue to work the problems and report back if and when the new RIGHT/FULL JOIN feature seems to be working better.

While it is nice that it is easy for you to do that, how does one make a "clone" repository consistent again?

Do I have to delete my private branch and start all over again?

Delete the entire clone repository and start al over again from scratch?

How do I get your "rolled back" changes to "roll back" out of private branches that they have been merged into?

How do I get your "rolled back" changes to "roll back" out of private branches that they have been merged into?

$ fossil merge --backout ABCD1234

where ABCD1234 is the commit you want backed out. You can give this multiple times to back out multiple commits.

Just "fossil sync" and all will be well. What problem did you observe?

THe only way I managed to get the changes out of my private branch was to:
1 - get rid of the checkout and wipe the subdirectories completely.
2 - SHUN all the merges and commits since your change
3 - rebuild the entire repository
4 - unshun the artifacts
5 - checkout my private branch (which appears to be OK)
6 - re-merge from trunk

I am now inspecting that all the changes have actually managed to make it. If not, I will simply start all over again with a new repository clone.

And no, sync did nothing. My private branch then simply contained a merge from right-join that would not go away.

RIGHT/FULL JOIN has relanded on trunk. A new snapshot tarball is available at https://sqlite.org/download.html.

Please try once again to break it. Report any problems you find on this thread, or on a new thread, or directly to me at drh at sqlite dot org.

Well, seems to be working just fine for me now. Thanks for the fix (and the new feature)!