Dear SQLite Developers,

I meet a wrong execution result. Please see the below cases. I can reproduce it on the latest Check-in a7be7d155a of trunk. I would like to express my gratitude to your efforts in bug fixing and detailed analysis!

CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c0 INT);
CREATE TABLE t2 (c0 INT);

INSERT INTO t1(c0) VALUES (NULL);
INSERT INTO t2 VALUES (1);

CREATE VIEW v0(c0) AS SELECT 1 AS col_0 FROM t0;

SELECT * FROM (t0 NATURAL RIGHT JOIN (t0 FULL JOIN (v0 NATURAL FULL JOIN t2) ON TRUE)) NATURAL FULL JOIN t1;
-- Wrong Execution Result:
-- NULL,1
-- NULL,NULL

-- Expected Result:
-- 1,1
-- NULL,NULL

This is the query I run on PostgreSQL (14.1):

CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c0 INT);
CREATE TABLE t2 (c0 INT);
INSERT INTO t1(c0) VALUES (NULL);
INSERT INTO t2 VALUES (1);
CREATE VIEW v0(c0) AS SELECT 1 FROM t0;

WITH x1        AS (SELECT * FROM v0 NATURAL FULL JOIN t2),
     x2(c0,c1) AS (SELECT * FROM t0 FULL JOIN x1 ON true),
     x3        AS (SELECT * FROM t0 NATURAL RIGHT JOIN x2)
SELECT * FROM x3 NATURAL FULL JOIN t1;

The above is equivalent to yours, is it not? I had to break up the query using common table expressions and assign column names to the x2 subquery because otherwise PG complains 'table name "t0" specified more than once' and 'common column name "c0" appears more than once in right table'. But the queries are equivalent, right? Am I missing something?

SQLite gets the same answer for this query as for the original. And PG and SQLite get the same answer for this query.

So why do you think the correct answer should be (1,1),(NULL,NULL)?

Thanks for your reply.

Because the the result of left part of natural full join is 1,1:

SELECT * FROM (t0 NATURAL RIGHT JOIN (t0 FULL JOIN (v0 NATURAL FULL JOIN t2) ON TRUE));
—— 1,1

Full join will preserve all the rows of both left part and right part, so I believe the expected result is (1,1),(NULL,NULL)

OK. So these queries get different answers in SQLite:

SELECT * FROM (t0 NATURAL RIGHT JOIN (t0 FULL JOIN (v0 NATURAL FULL JOIN t2) ON TRUE));
-- 1, 1

And

WITH x1        AS (SELECT * FROM v0 NATURAL FULL JOIN t2),
     x2(c0,c1) AS (SELECT * FROM t0 FULL JOIN x1 ON true)
SELECT * FROM t0 NATURAL RIGHT JOIN x2;
-- NULL, 1

The second one gets the same answer as PG. The first one does not run in PG; it throws the error: 'common column name "c0" appears more than once in right table'. I suspect that this overuse of column name "c0" is the cause of the output discrepancy in SQLite. We will investigate further and see what we can find.

The SQLite3.40 version produces differently with the latest version.

https://www.db-fiddle.com/f/5XGXC7E4cafY1nm4xdztMH/0

Specifically, the natural full join produces (1,1),(NULL,NULL)