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