aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/affinity3.test32
-rw-r--r--test/aggnested.test11
-rw-r--r--test/autoindex1.test2
-rw-r--r--test/autoindex4.test25
-rw-r--r--test/btree01.test3
-rw-r--r--test/collate2.test24
-rw-r--r--test/fts3join.test2
-rw-r--r--test/index6.test2
-rw-r--r--test/join.test24
-rw-r--r--test/join2.test31
-rw-r--r--test/join5.test6
-rw-r--r--test/join7.test273
-rw-r--r--test/join8.test24
-rw-r--r--test/vtab6.test14
-rw-r--r--test/where.test9
-rw-r--r--test/where9.test4
16 files changed, 449 insertions, 37 deletions
diff --git a/test/affinity3.test b/test/affinity3.test
index ef1533a8f..48942de72 100644
--- a/test/affinity3.test
+++ b/test/affinity3.test
@@ -30,11 +30,24 @@ do_execsql_test affinity3-100 {
FROM customer c
LEFT JOIN apr i ON i.id=c.id;
+ CREATE VIEW v1rj AS
+ SELECT c.id, i.apr
+ FROM apr i
+ RIGHT JOIN customer c ON i.id=c.id;
+
CREATE VIEW v2 AS
SELECT c.id, v1.apr
FROM customer c
LEFT JOIN v1 ON v1.id=c.id;
+ CREATE VIEW v2rj AS
+ SELECT c.id, v1.apr
+ FROM v1 RIGHT JOIN customer c ON v1.id=c.id;
+
+ CREATE VIEW v2rjrj AS
+ SELECT c.id, v1rj.apr
+ FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id;
+
INSERT INTO customer (id) VALUES (1);
INSERT INTO apr (id, apr) VALUES (1, 12);
INSERT INTO customer (id) VALUES (2);
@@ -44,16 +57,35 @@ do_execsql_test affinity3-110 {
PRAGMA automatic_index=ON;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-111 {
+ PRAGMA automatic_index=ON;
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj;
+} {1 0.12 real 2 0.1201 real}
do_execsql_test affinity3-120 {
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-121 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj;
+} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-122 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj;
+} {1 0.12 real 2 0.1201 real}
do_execsql_test affinity3-130 {
PRAGMA automatic_index=OFF;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-131 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj;
+} {1 0.12 real 2 0.1201 real}
do_execsql_test affinity3-140 {
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-141 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj;
+} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-142 {
+ SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj;
+} {1 0.12 real 2 0.1201 real}
# Ticket https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf (2017-01-16)
# Incorrect affinity when using automatic indexes
diff --git a/test/aggnested.test b/test/aggnested.test
index 35d5f1e3a..1b8b60880 100644
--- a/test/aggnested.test
+++ b/test/aggnested.test
@@ -137,6 +137,17 @@ do_test aggnested-3.1 {
GROUP BY curr.id1);
}
} {1 1}
+do_test aggnested-3.1-rj {
+ db eval {
+ SELECT
+ (SELECT sum(value2==xyz) FROM t2)
+ FROM
+ (SELECT curr.value1 as xyz
+ FROM t1 AS other RIGHT JOIN t1 AS curr
+ GROUP BY curr.id1);
+ }
+} {1 1}
+
do_test aggnested-3.2 {
db eval {
DROP TABLE IF EXISTS t1;
diff --git a/test/autoindex1.test b/test/autoindex1.test
index 6b437f186..2cd490040 100644
--- a/test/autoindex1.test
+++ b/test/autoindex1.test
@@ -283,7 +283,7 @@ do_eqp_test autoindex1-600a {
| `--CORRELATED SCALAR SUBQUERY xxxxxx
| `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
|--SCAN x USING INDEX sheep_reg_flock_index
- `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?)
+ `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
}
diff --git a/test/autoindex4.test b/test/autoindex4.test
index 24604af58..d9ab783e4 100644
--- a/test/autoindex4.test
+++ b/test/autoindex4.test
@@ -32,12 +32,21 @@ do_execsql_test autoindex4-1.1 {
do_execsql_test autoindex4-1.2 {
SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
+do_execsql_test autoindex4-1.2-rj {
+ SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON a=234 AND x=555;
+} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
do_execsql_test autoindex4-1.3 {
SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
} {234 def {} {} | 234 ghi {} {} |}
+do_execsql_test autoindex4-1.3-rj {
+ SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON x=555 WHERE a=234;
+} {234 def {} {} | 234 ghi {} {} |}
do_execsql_test autoindex4-1.4 {
SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
} {}
+do_execsql_test autoindex4-1.4-rj {
+ SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555;
+} {}
do_execsql_test autoindex4-2.0 {
@@ -69,6 +78,14 @@ do_execsql_test autoindex4-3.0 {
ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.1 {
+ SELECT Items.ItemName
+ FROM A
+ RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+ LEFT JOIN B ON (B.Name = Items.ItemName)
+ WHERE Items.Name = 'Parent'
+ ORDER BY Items.ItemName;
+} {Item1 Item2}
+do_execsql_test autoindex4-3.10 {
CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
SELECT Items.ItemName
@@ -78,6 +95,14 @@ do_execsql_test autoindex4-3.1 {
WHERE Items.Name = 'Parent'
ORDER BY Items.ItemName;
} {Item1 Item2}
+do_execsql_test autoindex4-3.11 {
+ SELECT Items.ItemName
+ FROM A
+ RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+ LEFT JOIN B ON (B.Name = Items.ItemName)
+ WHERE Items.Name = 'Parent'
+ ORDER BY Items.ItemName;
+} {Item1 Item2}
# 2021-11-30 - Enhancement to help the automatic index mechanism to
# create a partial index more often.
diff --git a/test/btree01.test b/test/btree01.test
index 9c309760d..6e4717ae6 100644
--- a/test/btree01.test
+++ b/test/btree01.test
@@ -148,6 +148,9 @@ do_execsql_test btree01-2.1 {
INSERT INTO t2(y) VALUES(198),(187),(100);
SELECT y, c FROM t2 LEFT JOIN t1 ON y=a ORDER BY x;
} {198 99 187 {} 100 50}
+do_execsql_test btree01-2.2 {
+ SELECT y, c FROM t1 RIGHT JOIN t2 ON y=a ORDER BY x;
+} {198 99 187 {} 100 50}
finish_test
diff --git a/test/collate2.test b/test/collate2.test
index d5aadb4eb..281aa3570 100644
--- a/test/collate2.test
+++ b/test/collate2.test
@@ -684,16 +684,34 @@ do_test collate2-5.3 {
SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
}
} {aa}
-do_test collate2-5.4 {
+do_test collate2-5.4.1 {
execsql {
- SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
+ SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 USING (b) order by collate2t1.oid;
}
} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
-do_test collate2-5.5 {
+do_test collate2-5.4.2 {
+ execsql {
+ SELECT collate2t2.b FROM collate2t2 RIGHT JOIN collate2t1 ON collate2t1.b=collate2t2.b
+ ORDER BY collate2t1.oid;
+ }
+} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
+do_test collate2-5.4.3 {
+ execsql {
+ SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 ON collate2t2.b=collate2t1.b
+ ORDER BY collate2t1.oid;
+ }
+} {{} aa {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
+do_test collate2-5.5.1 {
execsql {
SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);
}
} {aa aa}
+do_test collate2-5.5.2 {
+ execsql {
+ SELECT collate2t1.b, collate2t2.b
+ FROM collate2t1 RIGHT JOIN collate2t2 ON collate2t2.b=collate2t1.b
+ }
+} {aa aa}
do_execsql_test 6.1 {
CREATE TABLE t1(x);
diff --git a/test/fts3join.test b/test/fts3join.test
index 9a7994193..3333b1ab4 100644
--- a/test/fts3join.test
+++ b/test/fts3join.test
@@ -100,7 +100,7 @@ do_eqp_test 4.2 {
|--MATERIALIZE rr
| `--SCAN ft4 VIRTUAL TABLE INDEX 3:
|--SCAN t4
- `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?)
+ `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?) LEFT-JOIN
}
finish_test
diff --git a/test/index6.test b/test/index6.test
index 7eed6a47e..1ae2ee875 100644
--- a/test/index6.test
+++ b/test/index6.test
@@ -320,7 +320,7 @@ do_eqp_test index6-8.1 {
} {
QUERY PLAN
|--SCAN t8a
- `--SEARCH t8b USING INDEX i8c (y=?)
+ `--SEARCH t8b USING INDEX i8c (y=?) LEFT-JOIN
}
do_execsql_test index6-8.2 {
diff --git a/test/join.test b/test/join.test
index f48a1a149..d6e775436 100644
--- a/test/join.test
+++ b/test/join.test
@@ -272,11 +272,13 @@ do_test join-2.2 {
SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
}
} {1 2 3 {} 2 3 4 1 3 4 5 2}
-do_test join-2.3 {
- catchsql {
- SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
- }
-} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+
+#do_test join-2.3 {
+# catchsql {
+# SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
+# }
+#} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+
do_test join-2.4 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
@@ -330,32 +332,32 @@ do_test join-3.7 {
catchsql {
SELECT * FROM t1 INNER OUTER JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER OUTER}}
+} {1 {unknown join type: INNER OUTER}}
do_test join-3.8 {
catchsql {
SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER OUTER CROSS}}
+} {1 {unknown join type: INNER OUTER CROSS}}
do_test join-3.9 {
catchsql {
SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
}
-} {1 {unknown or unsupported join type: OUTER NATURAL INNER}}
+} {1 {unknown join type: OUTER NATURAL INNER}}
do_test join-3.10 {
catchsql {
SELECT * FROM t1 LEFT BOGUS JOIN t2;
}
-} {1 {unknown or unsupported join type: LEFT BOGUS}}
+} {1 {unknown join type: LEFT BOGUS}}
do_test join-3.11 {
catchsql {
SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER BOGUS CROSS}}
+} {1 {unknown join type: INNER BOGUS CROSS}}
do_test join-3.12 {
catchsql {
SELECT * FROM t1 NATURAL AWK SED JOIN t2;
}
-} {1 {unknown or unsupported join type: NATURAL AWK SED}}
+} {1 {unknown join type: NATURAL AWK SED}}
do_test join-4.1 {
execsql {
diff --git a/test/join2.test b/test/join2.test
index 170000ca2..4142fd15b 100644
--- a/test/join2.test
+++ b/test/join2.test
@@ -63,6 +63,12 @@ do_test join2-1.6 {
t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
}
} {1 11 111 1111}
+do_test join2-1.6-rj {
+ execsql {
+ SELECT * FROM
+ t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t3
+ }
+} {11 111 1 1111}
ifcapable subquery {
do_test join2-1.7 {
execsql {
@@ -70,6 +76,12 @@ ifcapable subquery {
t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
}
} {1 11 111 1111 2 22 {} {} 3 33 {} {}}
+ do_test join2-1.7-rj {
+ execsql {
+ SELECT * FROM
+ (t2 NATURAL JOIN t3) NATURAL RIGHT JOIN t1
+ }
+ } {11 111 1111 1 {} {} {} 2 {} {} {} 3}
}
#-------------------------------------------------------------------------
@@ -88,6 +100,9 @@ do_execsql_test 2.0 {
do_catchsql_test 2.1 {
SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
} {1 {ON clause references tables to its right}}
+do_catchsql_test 2.1b {
+ SELECT * FROM aa RIGHT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
+} {1 {ON clause references tables to its right}}
do_catchsql_test 2.2 {
SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
} {0 {one one one}}
@@ -114,7 +129,7 @@ do_eqp_test 3.1 {
} {
QUERY PLAN
|--SCAN t1
- `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
+ `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
do_eqp_test 3.2 {
@@ -122,7 +137,7 @@ do_eqp_test 3.2 {
} {
QUERY PLAN
|--SCAN t1
- `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
+ `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
#-------------------------------------------------------------------------
@@ -162,15 +177,15 @@ do_eqp_test 4.1.5 {
} {
QUERY PLAN
|--SCAN c1
- |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?)
- `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
+ |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
+ `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
do_eqp_test 4.1.6 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
QUERY PLAN
|--SCAN c1
- `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
+ `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
do_execsql_test 4.2.0 {
@@ -209,15 +224,15 @@ do_eqp_test 4.2.5 {
} {
QUERY PLAN
|--SCAN c1
- |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?)
- `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
+ |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN
+ `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
}
do_eqp_test 4.2.6 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
QUERY PLAN
|--SCAN c1
- `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
+ `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
}
# 2017-11-23 (Thanksgiving day)
diff --git a/test/join5.test b/test/join5.test
index a5cedd8cb..e4fd9cd87 100644
--- a/test/join5.test
+++ b/test/join5.test
@@ -280,9 +280,9 @@ do_eqp_test 7.2 {
|--SCAN t1
`--MULTI-INDEX OR
|--INDEX 1
- | `--SEARCH t2 USING INDEX t2xy (x=? AND y=?)
+ | `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
`--INDEX 2
- `--SEARCH t2 USING INDEX t2xy (x=? AND y=?)
+ `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
}
do_execsql_test 7.3 {
@@ -303,7 +303,7 @@ do_eqp_test 7.4 {
} {
QUERY PLAN
|--SCAN t3
- `--SEARCH t4 USING INDEX t4xz (x=?)
+ `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
}
do_eqp_test 7.4b {
SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);
diff --git a/test/join7.test b/test/join7.test
new file mode 100644
index 000000000..a0507be1d
--- /dev/null
+++ b/test/join7.test
@@ -0,0 +1,273 @@
+# 2022-04-09
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests for RIGHT and FULL OUTER JOINs.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+foreach {id schema} {
+ 1 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE INDEX t1a ON t1(a);
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE INDEX t2c ON t2(c);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 2 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE INDEX t1ab ON t1(a,b);
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE INDEX t2cd ON t2(c,d);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 3 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE INDEX t1a ON t1(a);
+ CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID;
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE INDEX t2c ON t2(c);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 4 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 5 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID;
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 6 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 7 {
+ CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 8 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ }
+ 9 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
+ CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
+ CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
+ INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
+ INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
+ CREATE TABLE dual(dummy TEXT);
+ INSERT INTO dual(dummy) VALUES('x');
+ }
+ 10 {
+ CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
+ INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+ CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
+ CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
+ CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
+ INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
+ INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
+ CREATE TABLE dual(dummy TEXT);
+ INSERT INTO dual(dummy) VALUES('x');
+ }
+} {
+ reset_db
+ db nullvalue NULL
+ do_execsql_test join7-$id.setup $schema {}
+ do_execsql_test join7-$id.10 {
+ SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL 55
+ 2 NULL
+ 3 33
+ 4 44
+ }
+ do_execsql_test join7-$id.20 {
+ SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL 5
+ 1 NULL
+ 1 3
+ 1 4
+ }
+ do_execsql_test join7-$id.30 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.31 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.40 {
+ SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.50 {
+ SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.60 {
+ SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL NULL 5 55
+ x 1 3 3 33
+ x 1 4 4 44
+ }
+ do_execsql_test join7-$id.70 {
+ SELECT t1.*, t2.*
+ FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.80 {
+ SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL NULL 5 55
+ x 1 3 3 33
+ x 1 4 4 44
+ }
+ do_execsql_test join7-$id.81 {
+ SELECT dual.*, t1.*, t2.*
+ FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ NULL NULL NULL 5 55
+ x 1 3 3 33
+ x 1 4 4 44
+ }
+ do_execsql_test join7-$id.90 {
+ SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b;
+ } {
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.100 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.101 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.110 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b;
+ } {
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.111 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE a=1 ORDER BY +b;
+ } {
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.115 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c
+ WHERE a=1 OR a IS NULL ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.116 {
+ SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
+ WHERE a=1 OR a IS NULL ORDER BY +b;
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 3 33
+ 1 4 4 44
+ }
+ do_execsql_test join7-$id.120 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d;
+ } {
+ NULL NULL 3 33
+ NULL NULL 4 44
+ NULL NULL 5 55
+ }
+ do_execsql_test join7-$id.130 {
+ SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d;
+ } {
+ NULL NULL 3 33
+ NULL NULL 4 44
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 NULL NULL
+ 1 4 NULL NULL
+ }
+ do_execsql_test join7-$id.140 {
+ SELECT a, b, c, d
+ FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d;
+ } {
+ NULL NULL 3 33
+ NULL NULL 4 44
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ 1 3 NULL NULL
+ 1 4 NULL NULL
+ }
+ do_execsql_test join7-$id.141 {
+ SELECT a, b, c, d
+ FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0
+ ORDER BY +b, +d LIMIT 2 OFFSET 2
+ } {
+ NULL NULL 5 55
+ 1 2 NULL NULL
+ }
+}
+finish_test
diff --git a/test/join8.test b/test/join8.test
new file mode 100644
index 000000000..0854d9729
--- /dev/null
+++ b/test/join8.test
@@ -0,0 +1,24 @@
+# 2022-04-12
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# This file implements tests for RIGHT and FULL OUTER JOINs.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+db null NULL
+do_execsql_test join8-10 {
+ CREATE TABLE t1(a,b,c);
+ CREATE TABLE t2(x,y);
+ CREATE INDEX t2x ON t2(x);
+ SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c);
+} {NULL}
+finish_test
diff --git a/test/vtab6.test b/test/vtab6.test
index ab52c9f15..2ee5e2705 100644
--- a/test/vtab6.test
+++ b/test/vtab6.test
@@ -223,11 +223,11 @@ do_test vtab6-2.2 {
SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
}
} {1 2 3 {} 2 3 4 1 3 4 5 2}
-do_test vtab6-2.3 {
- catchsql {
- SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
- }
-} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+#do_test vtab6-2.3 {
+# catchsql {
+# SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
+# }
+#} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
do_test vtab6-2.4 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
@@ -281,12 +281,12 @@ do_test vtab6-3.7 {
catchsql {
SELECT * FROM t1 INNER OUTER JOIN t2;
}
-} {1 {unknown or unsupported join type: INNER OUTER}}
+} {1 {unknown join type: INNER OUTER}}
do_test vtab6-3.7 {
catchsql {
SELECT * FROM t1 LEFT BOGUS JOIN t2;
}
-} {1 {unknown or unsupported join type: LEFT BOGUS}}
+} {1 {unknown join type: LEFT BOGUS}}
do_test vtab6-4.1 {
execsql {
diff --git a/test/where.test b/test/where.test
index 8ee57b8b6..2f53f2cb4 100644
--- a/test/where.test
+++ b/test/where.test
@@ -1348,16 +1348,25 @@ do_execsql_test where-18.1 {
INSERT INTO t181 VALUES(1);
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
} {1}
+do_execsql_test where-18.1rj {
+ SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c IS NULL;
+} {1}
do_execsql_test where-18.2 {
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
} {1}
do_execsql_test where-18.3 {
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
} {1}
+do_execsql_test where-18.3rj {
+ SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c;
+} {1}
do_execsql_test where-18.4 {
INSERT INTO t181 VALUES(1),(1),(1),(1);
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
} {1}
+do_execsql_test where-18.4rj {
+ SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY +c;
+} {1}
do_execsql_test where-18.5 {
INSERT INTO t181 VALUES(2);
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
diff --git a/test/where9.test b/test/where9.test
index 429708f1f..475788153 100644
--- a/test/where9.test
+++ b/test/where9.test
@@ -378,9 +378,9 @@ ifcapable explain {
|--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
`--MULTI-INDEX OR
|--INDEX 1
- | `--SEARCH t2 USING INDEX t2d (d=?)
+ | `--SEARCH t2 USING INDEX t2d (d=?) LEFT-JOIN
`--INDEX 2
- `--SEARCH t2 USING COVERING INDEX t2f (f=?)
+ `--SEARCH t2 USING COVERING INDEX t2f (f=?) LEFT-JOIN
}]
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 18:35:33 +0000