1# 2022-04-16 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 t3(id INTEGER PRIMARY KEY, w TEXT); 21 CREATE TABLE t4(id INTEGER PRIMARY KEY, x TEXT); 22 CREATE TABLE t5(id INTEGER PRIMARY KEY, y TEXT); 23 CREATE TABLE t6(id INTEGER PRIMARY KEY, z INT); 24 CREATE VIEW dual(dummy) AS VALUES('x'); 25 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); 26 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); 27 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), 28 (5,'blue'); 29 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); 30 } 31 2 { 32 CREATE TABLE t3(id INT PRIMARY KEY, w TEXT) WITHOUT ROWID; 33 CREATE TABLE t4(id INT PRIMARY KEY, x TEXT) WITHOUT ROWID; 34 CREATE TABLE t5(id INT PRIMARY KEY, y TEXT) WITHOUT ROWID; 35 CREATE TABLE t6(id INT PRIMARY KEY, z INT) WITHOUT ROWID; 36 CREATE VIEW dual(dummy) AS VALUES('x'); 37 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); 38 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); 39 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), 40 (5,'blue'); 41 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); 42 } 43} { 44 reset_db 45 db nullvalue - 46 do_execsql_test join9-$id.setup $schema {} 47 48 # Verifid by PG-14 for case 1 49 do_execsql_test join9-$id.100 { 50 SELECT *, t4.id, t5.id, t6.id 51 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 52 ORDER BY 1; 53 } { 54 2 alice orange - 2 2 - 55 4 bob green 444 4 4 4 56 6 cindy - - 6 - - 57 8 dave - - 8 - - 58 } 59 60 do_execsql_test join9-$id.101 { 61 SELECT *, t4.id, t5.id, t6.id 62 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 63 ORDER BY id; 64 } { 65 2 alice orange - 2 2 - 66 4 bob green 444 4 4 4 67 6 cindy - - 6 - - 68 8 dave - - 8 - - 69 } 70 do_execsql_test join9-$id.102 { 71 SELECT *, t4.id, t5.id, t6.id 72 FROM t4 LEFT JOIN t5 USING(id) LEFT JOIN t6 USING(id) 73 ORDER BY id; 74 } { 75 2 alice orange - 2 2 - 76 4 bob green 444 4 4 4 77 6 cindy - - 6 - - 78 8 dave - - 8 - - 79 } 80 81 # Verifid by PG-14 using case 1 82 do_execsql_test join9-$id.200 { 83 SELECT id, x, y, z, t4.id, t5.id, t6.id 84 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6 85 ORDER BY 1; 86 } { 87 2 alice orange - 2 2 - 88 4 bob green 444 4 4 4 89 6 cindy - - 6 - - 90 8 dave - - 8 - - 91 } 92 93 do_execsql_test join9-$id.201 { 94 SELECT id, x, y, z, t4.id, t5.id, t6.id 95 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6 96 ORDER BY id; 97 } { 98 2 alice orange - 2 2 - 99 4 bob green 444 4 4 4 100 6 cindy - - 6 - - 101 8 dave - - 8 - - 102 } 103 104 # Verified by PG-14 using case 1 105 do_execsql_test join9-$id.300 { 106 SELECT *, t4.id, t5.id, t6.id 107 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 108 ORDER BY 1; 109 } { 110 0 - - 1000 - - 0 111 3 - yellow 333 - 3 3 112 4 bob green 444 4 4 4 113 5 - blue 555 - 5 5 114 9 - - 999 - - 9 115 } 116 117 do_execsql_test join9-$id.301 { 118 SELECT *, t4.id, t5.id, t6.id 119 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 120 ORDER BY id; 121 } { 122 0 - - 1000 - - 0 123 3 - yellow 333 - 3 3 124 4 bob green 444 4 4 4 125 5 - blue 555 - 5 5 126 9 - - 999 - - 9 127 } 128 129 # Verified by PG-14 for case 1 130 do_execsql_test join9-$id.400 { 131 SELECT *, t4.id, t5.id, t6.id 132 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 133 ORDER BY 1; 134 } { 135 0 - - 1000 - - 0 136 1 - red - - 1 - 137 2 alice orange - 2 2 - 138 3 - yellow 333 - 3 3 139 4 bob green 444 4 4 4 140 5 - blue 555 - 5 5 141 6 cindy - - 6 - - 142 8 dave - - 8 - - 143 9 - - 999 - - 9 144 } 145 146 do_execsql_test join9-$id.401 { 147 SELECT *, t4.id, t5.id, t6.id 148 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 149 ORDER BY id; 150 } { 151 0 - - 1000 - - 0 152 1 - red - - 1 - 153 2 alice orange - 2 2 - 154 3 - yellow 333 - 3 3 155 4 bob green 444 4 4 4 156 5 - blue 555 - 5 5 157 6 cindy - - 6 - - 158 8 dave - - 8 - - 159 9 - - 999 - - 9 160 } 161 do_execsql_test join9-$id.402 { 162 SELECT id, x, y, z, t4.id, t5.id, t6.id 163 FROM t4 NATURAL FULL JOIN t6 NATURAL FULL JOIN t5 164 ORDER BY id; 165 } { 166 0 - - 1000 - - 0 167 1 - red - - 1 - 168 2 alice orange - 2 2 - 169 3 - yellow 333 - 3 3 170 4 bob green 444 4 4 4 171 5 - blue 555 - 5 5 172 6 cindy - - 6 - - 173 8 dave - - 8 - - 174 9 - - 999 - - 9 175 } 176 do_execsql_test join9-$id.403 { 177 SELECT id, x, y, z, t4.id, t5.id, t6.id 178 FROM t5 NATURAL FULL JOIN t4 NATURAL FULL JOIN t6 179 ORDER BY id; 180 } { 181 0 - - 1000 - - 0 182 1 - red - - 1 - 183 2 alice orange - 2 2 - 184 3 - yellow 333 - 3 3 185 4 bob green 444 4 4 4 186 5 - blue 555 - 5 5 187 6 cindy - - 6 - - 188 8 dave - - 8 - - 189 9 - - 999 - - 9 190 } 191 do_execsql_test join9-$id.404 { 192 SELECT id, x, y, z, t4.id, t5.id, t6.id 193 FROM t5 NATURAL FULL JOIN t6 NATURAL FULL JOIN t4 194 ORDER BY id; 195 } { 196 0 - - 1000 - - 0 197 1 - red - - 1 - 198 2 alice orange - 2 2 - 199 3 - yellow 333 - 3 3 200 4 bob green 444 4 4 4 201 5 - blue 555 - 5 5 202 6 cindy - - 6 - - 203 8 dave - - 8 - - 204 9 - - 999 - - 9 205 } 206 do_execsql_test join9-$id.405 { 207 SELECT id, x, y, z, t4.id, t5.id, t6.id 208 FROM t6 NATURAL FULL JOIN t4 NATURAL FULL JOIN t5 209 ORDER BY id; 210 } { 211 0 - - 1000 - - 0 212 1 - red - - 1 - 213 2 alice orange - 2 2 - 214 3 - yellow 333 - 3 3 215 4 bob green 444 4 4 4 216 5 - blue 555 - 5 5 217 6 cindy - - 6 - - 218 8 dave - - 8 - - 219 9 - - 999 - - 9 220 } 221 do_execsql_test join9-$id.406 { 222 SELECT id, x, y, z, t4.id, t5.id, t6.id 223 FROM t6 NATURAL FULL JOIN t5 NATURAL FULL JOIN t4 224 ORDER BY id; 225 } { 226 0 - - 1000 - - 0 227 1 - red - - 1 - 228 2 alice orange - 2 2 - 229 3 - yellow 333 - 3 3 230 4 bob green 444 4 4 4 231 5 - blue 555 - 5 5 232 6 cindy - - 6 - - 233 8 dave - - 8 - - 234 9 - - 999 - - 9 235 } 236 237 # Verified by PG-14 using case 1 238 do_execsql_test join9-$id.500 { 239 SELECT id, w, x, y, z 240 FROM t3 FULL JOIN t4 USING(id) 241 NATURAL FULL JOIN t5 242 FULL JOIN t6 USING(id) 243 ORDER BY 1; 244 } { 245 0 - - - 1000 246 1 - - red - 247 2 two alice orange - 248 3 three - yellow 333 249 4 - bob green 444 250 5 - - blue 555 251 6 six cindy - - 252 7 seven - - - 253 8 - dave - - 254 9 - - - 999 255 } 256 257 # Verified by PG-14 using case 1 258 do_execsql_test join9-$id.600 { 259 SELECT id, w, x, y, z 260 FROM t3 JOIN dual ON true 261 FULL JOIN t4 USING(id) 262 JOIN dual AS d2 ON true 263 NATURAL FULL JOIN t5 264 JOIN dual AS d3 ON true FULL 265 JOIN t6 USING(id) 266 CROSS JOIN dual AS d4 267 ORDER BY 1; 268 } { 269 0 - - - 1000 270 1 - - red - 271 2 two alice orange - 272 3 three - yellow 333 273 4 - bob green 444 274 5 - - blue 555 275 6 six cindy - - 276 7 seven - - - 277 8 - dave - - 278 9 - - - 999 279 } 280} 281finish_test 282