1# 2022-04-18 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, c INT, d INT); 21 CREATE TABLE t2(c INT, d INT, e INT, f INT); 22 CREATE TABLE t3(a INT, b INT, e INT, f INT); 23 CREATE TABLE t4(a INT, c INT, d INT, f INT); 24 INSERT INTO t1 VALUES(11,21,31,41),(12,22,32,42),(15,25,35,45),(18,28,38,48); 25 INSERT INTO t2 VALUES(12,22,32,42),(13,23,33,43),(15,25,35,45),(17,27,37,47); 26 INSERT INTO t3 VALUES(14,24,34,44),(15,25,35,45),(16,26,36,46); 27 INSERT INTO t4 VALUES(11,21,31,41),(13,23,33,43),(16,26,36,46),(19,29,39,49); 28 } 29 2 { 30 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT); 31 CREATE TABLE t2(c INT, d INTEGER PRIMARY KEY, e INT, f INT); 32 CREATE TABLE t3(a INT, b INT, e INTEGER PRIMARY KEY, f INT); 33 CREATE TABLE t4(a INT, c INT, d INT, f INT PRIMARY KEY) WITHOUT ROWID; 34 INSERT INTO t1 VALUES(11,21,31,41),(12,22,32,42),(15,25,35,45),(18,28,38,48); 35 INSERT INTO t2 VALUES(12,22,32,42),(13,23,33,43),(15,25,35,45),(17,27,37,47); 36 INSERT INTO t3 VALUES(14,24,34,44),(15,25,35,45),(16,26,36,46); 37 INSERT INTO t4 VALUES(11,21,31,41),(13,23,33,43),(16,26,36,46),(19,29,39,49); 38 } 39 3 { 40 CREATE TABLE t1a(a INT, b INT, c INT, d INT); 41 CREATE TABLE t2a(c INT, d INT, e INT, f INT); 42 CREATE TABLE t3a(a INT, b INT, e INT, f INT); 43 CREATE TABLE t4a(a INT, c INT, d INT, f INT); 44 INSERT INTO t1a VALUES(11,21,31,41),(12,22,32,42); 45 INSERT INTO t2a VALUES(12,22,32,42),(13,23,33,43); 46 INSERT INTO t3a VALUES(14,24,34,44),(15,25,35,45); 47 INSERT INTO t4a VALUES(11,21,31,41),(13,23,33,43); 48 CREATE TABLE t1b(a INT, b INT, c INT, d INT); 49 CREATE TABLE t2b(c INT, d INT, e INT, f INT); 50 CREATE TABLE t3b(a INT, b INT, e INT, f INT); 51 CREATE TABLE t4b(a INT, c INT, d INT, f INT); 52 INSERT INTO t1b VALUES(15,25,35,45),(18,28,38,48); 53 INSERT INTO t2b VALUES(15,25,35,45),(17,27,37,47); 54 INSERT INTO t3b VALUES(15,25,35,45),(16,26,36,46); 55 INSERT INTO t4b VALUES(16,26,36,46),(19,29,39,49); 56 CREATE VIEW t1 AS SELECT * FROM t1a UNION SELECT * FROM t1b; 57 CREATE VIEW t2 AS SELECT * FROM t2a UNION SELECT * FROM t2b; 58 CREATE VIEW t3 AS SELECT * FROM t3a UNION SELECT * FROM t3b; 59 CREATE VIEW t4 AS SELECT * FROM t4a UNION SELECT * FROM t4b; 60 } 61} { 62 reset_db 63 db nullvalue - 64 do_execsql_test joinA-$id.setup $schema {} 65 66 # Verified by PG-14 67 do_execsql_test joinA-$id.100 { 68 SELECT a,b,c,d,t2.e,f,t3.e 69 FROM t1 70 INNER JOIN t2 USING(c,d) 71 INNER JOIN t3 USING(a,b,f) 72 INNER JOIN t4 USING(a,c,d,f) 73 ORDER BY 1 nulls first, 3 nulls first; 74 } {} 75 76 77 # Verified by PG-14 78 do_execsql_test joinA-$id.110 { 79 SELECT a,b,c,d,t2.e,f,t3.e 80 FROM t1 81 LEFT JOIN t2 USING(c,d) 82 LEFT JOIN t3 USING(a,b,f) 83 LEFT JOIN t4 USING(a,c,d,f) 84 ORDER BY 1 nulls first, 3 nulls first; 85 } { 86 11 21 31 41 - - - 87 12 22 32 42 - - - 88 15 25 35 45 - - - 89 18 28 38 48 - - - 90 } 91 92 # Verified by PG-14 93 do_execsql_test joinA-$id.120 { 94 SELECT a,b,c,d,t2.e,f,t3.e 95 FROM t1 96 LEFT JOIN t2 USING(c,d) 97 RIGHT JOIN t3 USING(a,b,f) 98 LEFT JOIN t4 USING(a,c,d,f) 99 ORDER BY 1 nulls first, 3 nulls first; 100 } { 101 14 24 - - - 44 34 102 15 25 - - - 45 35 103 16 26 - - - 46 36 104 } 105 106 # Verified by PG-14 107 do_execsql_test joinA-$id.130 { 108 SELECT a,b,c,d,t2.e,f,t3.e 109 FROM t1 110 RIGHT JOIN t2 USING(c,d) 111 LEFT JOIN t3 USING(a,b,f) 112 RIGHT JOIN t4 USING(a,c,d,f) 113 ORDER BY 1 nulls first, 3 nulls first; 114 } { 115 11 - 21 31 - 41 - 116 13 - 23 33 - 43 - 117 16 - 26 36 - 46 - 118 19 - 29 39 - 49 - 119 } 120 121 # Verified by PG-14 122 do_execsql_test joinA-$id.140 { 123 SELECT a,b,c,d,t2.e,f,t3.e 124 FROM t1 125 FULL JOIN t2 USING(c,d) 126 LEFT JOIN t3 USING(a,b,f) 127 RIGHT JOIN t4 USING(a,c,d,f) 128 ORDER BY 1 nulls first, 3 nulls first; 129 } { 130 11 - 21 31 - 41 - 131 13 - 23 33 - 43 - 132 16 - 26 36 - 46 - 133 19 - 29 39 - 49 - 134 } 135 136 # Verified by PG-14 137 do_execsql_test joinA-$id.150 { 138 SELECT a,b,c,d,t2.e,f,t3.e 139 FROM t1 140 RIGHT JOIN t2 USING(c,d) 141 FULL JOIN t3 USING(a,b,f) 142 RIGHT JOIN t4 USING(a,c,d,f) 143 ORDER BY 1 nulls first, 3 nulls first; 144 } { 145 11 - 21 31 - 41 - 146 13 - 23 33 - 43 - 147 16 - 26 36 - 46 - 148 19 - 29 39 - 49 - 149 } 150 151 # Verified by PG-14 152 do_execsql_test joinA-$id.160 { 153 SELECT a,b,c,d,t2.e,f,t3.e 154 FROM t1 155 RIGHT JOIN t2 USING(c,d) 156 LEFT JOIN t3 USING(a,b,f) 157 FULL JOIN t4 USING(a,c,d,f) 158 ORDER BY 1 nulls first, 3 nulls first; 159 } { 160 - - 12 22 32 42 - 161 - - 13 23 33 43 - 162 - - 15 25 35 45 - 163 - - 17 27 37 47 - 164 11 - 21 31 - 41 - 165 13 - 23 33 - 43 - 166 16 - 26 36 - 46 - 167 19 - 29 39 - 49 - 168 } 169 170 # Verified by PG-14 171 do_execsql_test joinA-$id.170 { 172 SELECT a,b,c,d,t2.e,f,t3.e 173 FROM t1 174 LEFT JOIN t2 USING(c,d) 175 RIGHT JOIN t3 USING(a,b,f) 176 FULL JOIN t4 USING(a,c,d,f) 177 ORDER BY 1 nulls first, 3 nulls first; 178 } { 179 11 - 21 31 - 41 - 180 13 - 23 33 - 43 - 181 14 24 - - - 44 34 182 15 25 - - - 45 35 183 16 26 - - - 46 36 184 16 - 26 36 - 46 - 185 19 - 29 39 - 49 - 186 } 187 188 # Verified by PG-14 189 do_execsql_test joinA-$id.200 { 190 SELECT a,b,c,d,t2.e,f,t3.e 191 FROM t1 192 FULL JOIN t2 USING(c,d) 193 FULL JOIN t3 USING(a,b,f) 194 FULL JOIN t4 USING(a,c,d,f) 195 ORDER BY 1 nulls first, 3 nulls first; 196 } { 197 - - 12 22 32 42 - 198 - - 13 23 33 43 - 199 - - 15 25 35 45 - 200 - - 17 27 37 47 - 201 11 - 21 31 - 41 - 202 11 21 31 41 - - - 203 12 22 32 42 - - - 204 13 - 23 33 - 43 - 205 14 24 - - - 44 34 206 15 25 - - - 45 35 207 15 25 35 45 - - - 208 16 26 - - - 46 36 209 16 - 26 36 - 46 - 210 18 28 38 48 - - - 211 19 - 29 39 - 49 - 212 } 213} 214finish_test 215