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} { 80 reset_db 81 db nullvalue NULL 82 do_execsql_test join7-$id.setup $schema {} 83 do_execsql_test join7-$id.1 { 84 SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 85 } { 86 NULL 55 87 2 NULL 88 3 33 89 4 44 90 } 91 do_execsql_test join7-$id.2 { 92 SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 93 } { 94 NULL 5 95 1 NULL 96 1 3 97 1 4 98 } 99 do_execsql_test join7-$id.3 { 100 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 101 } { 102 NULL NULL 5 55 103 1 2 NULL NULL 104 1 3 3 33 105 1 4 4 44 106 } 107 do_execsql_test join7-$id.4 { 108 SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 109 } { 110 NULL NULL 5 55 111 1 3 3 33 112 1 4 4 44 113 } 114 do_execsql_test join7-$id.5 { 115 SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 116 } { 117 NULL NULL NULL 5 55 118 x 1 3 3 33 119 x 1 4 4 44 120 } 121 do_execsql_test join7-$id.6 { 122 SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 123 } { 124 NULL NULL NULL 5 55 125 x 1 3 3 33 126 x 1 4 4 44 127 } 128 do_execsql_test join7-$id.7 { 129 SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b; 130 } { 131 1 2 NULL NULL 132 1 3 3 33 133 1 4 4 44 134 } 135 do_execsql_test join7-$id.8 { 136 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b; 137 } { 138 NULL NULL 5 55 139 1 2 NULL NULL 140 1 3 3 33 141 1 4 4 44 142 } 143 do_execsql_test join7-$id.9 { 144 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b; 145 } { 146 1 2 NULL NULL 147 1 3 3 33 148 1 4 4 44 149 } 150 do_execsql_test join7-$id.10 { 151 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d; 152 } { 153 NULL NULL 3 33 154 NULL NULL 4 44 155 NULL NULL 5 55 156 } 157 do_execsql_test join7-$id.11 { 158 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d; 159 } { 160 NULL NULL 3 33 161 NULL NULL 4 44 162 NULL NULL 5 55 163 1 2 NULL NULL 164 1 3 NULL NULL 165 1 4 NULL NULL 166 } 167} 168finish_test 169