1# 2022-04-09 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13# This file implements tests for RIGHT and FULL OUTER JOINs. 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18foreach {id schema} { 19 1 { 20 CREATE TABLE t1(a INT, b INT); 21 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 22 CREATE INDEX t1a ON t1(a); 23 CREATE TABLE t2(c INT, d INT); 24 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 25 CREATE INDEX t2c ON t2(c); 26 CREATE VIEW dual(dummy) AS VALUES('x'); 27 } 28 2 { 29 CREATE TABLE t1(a INT, b INT); 30 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 31 CREATE INDEX t1ab ON t1(a,b); 32 CREATE TABLE t2(c INT, d INT); 33 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 34 CREATE INDEX t2cd ON t2(c,d); 35 CREATE VIEW dual(dummy) AS VALUES('x'); 36 } 37 3 { 38 CREATE TABLE t1(a INT, b INT); 39 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 40 CREATE INDEX t1a ON t1(a); 41 CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID; 42 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 43 CREATE INDEX t2c ON t2(c); 44 CREATE VIEW dual(dummy) AS VALUES('x'); 45 } 46 4 { 47 CREATE TABLE t1(a INT, b INT); 48 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 49 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT); 50 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 51 CREATE VIEW dual(dummy) AS VALUES('x'); 52 } 53 5 { 54 CREATE TABLE t1(a INT, b INT); 55 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 56 CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID; 57 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 58 CREATE VIEW dual(dummy) AS VALUES('x'); 59 } 60 6 { 61 CREATE TABLE t1(a INT, b INT); 62 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 63 CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55); 64 CREATE VIEW dual(dummy) AS VALUES('x'); 65 } 66 7 { 67 CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4); 68 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT); 69 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 70 CREATE VIEW dual(dummy) AS VALUES('x'); 71 } 72 8 { 73 CREATE TABLE t1(a INT, b INT); 74 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 75 CREATE TABLE t2(c INT, d INT); 76 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 77 CREATE VIEW dual(dummy) AS VALUES('x'); 78 } 79 9 { 80 CREATE TABLE t1(a INT, b INT); 81 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 82 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT); 83 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT); 84 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b; 85 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97); 86 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55); 87 CREATE TABLE dual(dummy TEXT); 88 INSERT INTO dual(dummy) VALUES('x'); 89 } 90} { 91 reset_db 92 db nullvalue NULL 93 do_execsql_test join7-$id.setup $schema {} 94 do_execsql_test join7-$id.1 { 95 SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 96 } { 97 NULL 55 98 2 NULL 99 3 33 100 4 44 101 } 102 do_execsql_test join7-$id.2 { 103 SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 104 } { 105 NULL 5 106 1 NULL 107 1 3 108 1 4 109 } 110 do_execsql_test join7-$id.3 { 111 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 112 } { 113 NULL NULL 5 55 114 1 2 NULL NULL 115 1 3 3 33 116 1 4 4 44 117 } 118 do_execsql_test join7-$id.4 { 119 SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 120 } { 121 NULL NULL 5 55 122 1 3 3 33 123 1 4 4 44 124 } 125 do_execsql_test join7-$id.5 { 126 SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 127 } { 128 NULL NULL NULL 5 55 129 x 1 3 3 33 130 x 1 4 4 44 131 } 132 do_execsql_test join7-$id.6 { 133 SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 134 } { 135 NULL NULL NULL 5 55 136 x 1 3 3 33 137 x 1 4 4 44 138 } 139 do_execsql_test join7-$id.7 { 140 SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b; 141 } { 142 1 2 NULL NULL 143 1 3 3 33 144 1 4 4 44 145 } 146 do_execsql_test join7-$id.8 { 147 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b; 148 } { 149 NULL NULL 5 55 150 1 2 NULL NULL 151 1 3 3 33 152 1 4 4 44 153 } 154 do_execsql_test join7-$id.9 { 155 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b; 156 } { 157 1 2 NULL NULL 158 1 3 3 33 159 1 4 4 44 160 } 161 do_execsql_test join7-$id.10 { 162 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d; 163 } { 164 NULL NULL 3 33 165 NULL NULL 4 44 166 NULL NULL 5 55 167 } 168 do_execsql_test join7-$id.11 { 169 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d; 170 } { 171 NULL NULL 3 33 172 NULL NULL 4 44 173 NULL NULL 5 55 174 1 2 NULL NULL 175 1 3 NULL NULL 176 1 4 NULL NULL 177 } 178} 179finish_test 180