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} { 32 reset_db 33 db nullvalue - 34 do_execsql_test join9-$id.setup $schema {} 35 36 # Verifid by PG-14 for case 1 37 do_execsql_test join9-$id.100 { 38 SELECT *, t4.id, t5.id, t6.id 39 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 40 ORDER BY 1; 41 } { 42 2 alice orange - 2 2 - 43 4 bob green 444 4 4 4 44 6 cindy - - 6 - - 45 8 dave - - 8 - - 46 } 47 48 do_execsql_test join9-$id.101 { 49 SELECT *, t4.id, t5.id, t6.id 50 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 51 ORDER BY id; 52 } { 53 2 alice orange - 2 2 - 54 4 bob green 444 4 4 4 55 6 cindy - - 6 - - 56 8 dave - - 8 - - 57 } 58 do_execsql_test join9-$id.102 { 59 SELECT *, t4.id, t5.id, t6.id 60 FROM t4 LEFT JOIN t5 USING(id) LEFT JOIN t6 USING(id) 61 ORDER BY id; 62 } { 63 2 alice orange - 2 2 - 64 4 bob green 444 4 4 4 65 6 cindy - - 6 - - 66 8 dave - - 8 - - 67 } 68 69 # Verifid by PG-14 using case 1 70 do_execsql_test join9-$id.200 { 71 SELECT id, x, y, z, t4.id, t5.id, t6.id 72 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6 73 ORDER BY 1; 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 do_execsql_test join9-$id.201 { 82 SELECT id, x, y, z, t4.id, t5.id, t6.id 83 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6 84 ORDER BY id; 85 } { 86 2 alice orange - 2 2 - 87 4 bob green 444 4 4 4 88 6 cindy - - 6 - - 89 8 dave - - 8 - - 90 } 91 92 # Verified by PG-14 using case 1 93 do_execsql_test join9-$id.300 { 94 SELECT *, t4.id, t5.id, t6.id 95 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 96 ORDER BY 1; 97 } { 98 0 - - 1000 - - 0 99 3 - yellow 333 - 3 3 100 4 bob green 444 4 4 4 101 5 - blue 555 - 5 5 102 9 - - 999 - - 9 103 } 104 105 do_execsql_test join9-$id.301 { 106 SELECT *, t4.id, t5.id, t6.id 107 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 108 ORDER BY id; 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 # Verified by PG-14 for case 1 118 do_execsql_test join9-$id.400 { 119 SELECT *, t4.id, t5.id, t6.id 120 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 121 ORDER BY 1; 122 } { 123 0 - - 1000 - - 0 124 1 - red - - 1 - 125 2 alice orange - 2 2 - 126 3 - yellow 333 - 3 3 127 4 bob green 444 4 4 4 128 5 - blue 555 - 5 5 129 6 cindy - - 6 - - 130 8 dave - - 8 - - 131 9 - - 999 - - 9 132 } 133 134 do_execsql_test join9-$id.401 { 135 SELECT *, t4.id, t5.id, t6.id 136 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 137 ORDER BY id; 138 } { 139 0 - - 1000 - - 0 140 1 - red - - 1 - 141 2 alice orange - 2 2 - 142 3 - yellow 333 - 3 3 143 4 bob green 444 4 4 4 144 5 - blue 555 - 5 5 145 6 cindy - - 6 - - 146 8 dave - - 8 - - 147 9 - - 999 - - 9 148 } 149 do_execsql_test join9-$id.402 { 150 SELECT id, x, y, z, t4.id, t5.id, t6.id 151 FROM t4 NATURAL FULL JOIN t6 NATURAL FULL JOIN t5 152 ORDER BY id; 153 } { 154 0 - - 1000 - - 0 155 1 - red - - 1 - 156 2 alice orange - 2 2 - 157 3 - yellow 333 - 3 3 158 4 bob green 444 4 4 4 159 5 - blue 555 - 5 5 160 6 cindy - - 6 - - 161 8 dave - - 8 - - 162 9 - - 999 - - 9 163 } 164} 165finish_test 166