When synthesizing an ON constraint from a USING or NATURAL, if the left-hand

side is coming from a RIGHT JOIN, be sure to set the EP_CanBeNull flag so that
the optimizer knows to check for NULL even if the column has a NOT NULL
constraint.  Fix for the problem reported by
[forum:/forumpost/4fc70203b61c7e12|forum post 4fc70203b61]

FossilOrigin-Name: 60adc78a22956429d34ccc4e2c193c5994c11c3b3cff7901d47fad7d92dba935

1 files changed, 28 insertions, 0 deletions

diff --git a/test/join.test b/test/join.test
index ecd7bb2b5..789ae0124 100644
--- a/test/join.test
+++ b/test/join.test
@@ -1304,4 +1304,32 @@ do_execsql_test join-30.3 {
    WHERE x <= y;
 } {}
 
+# 2025-05-30 https://sqlite.org/forum/forumpost/4fc70203b61c7e12
+#
+# When converting a USING(x) or NATURAL into the constraint expression
+# t1.x==t2.x, mark the t1.x term as EP_CanBeNull if it is the left table
+# of a RIGHT JOIN.
+#
+reset_db
+db null NULL
+do_execsql_test join-31.1 {
+  CREATE TABLE t1(c0 INT , c1 INT); INSERT INTO t1(c0, c1) VALUES(NULL,11);
+  CREATE TABLE t2(c0 INT NOT NULL);
+  CREATE TABLE t2n(c0 INT);
+  CREATE TABLE t3(x INT);           INSERT INTO t3(x) VALUES(4);
+  CREATE TABLE t5(c0 INT, x INT);   INSERT INTO t5 VALUES(NULL, 4);
+}
+do_execsql_test join-31.2 {
+  SELECT * FROM t2 RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0);
+} {NULL 4 NULL}
+do_execsql_test join-31.3 {
+  SELECT * FROM t2 RIGHT JOIN t3 ON true NATURAL LEFT JOIN t1;
+} {NULL 4 NULL}
+do_execsql_test join-31.4 {
+  SELECT * FROM t2n RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0);
+} {NULL 4 NULL}
+do_execsql_test join-31.5 {
+  SELECT * FROM t5 LEFT JOIN t1 USING(c0);
+} {NULL 4 NULL}
+
 finish_test