AryaWu/sqlite
0
1# 2022-04-182#3# The author disclaims copyright to this source code. In place of4# 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.tcl17 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_db63 db nullvalue -64 do_execsql_test joinA-$id.setup $schema {}65 66 # Verified by PG-1467 do_execsql_test joinA-$id.100 {68 SELECT a,b,c,d,t2.e,f,t3.e69 FROM t170 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-1478 do_execsql_test joinA-$id.110 {79 SELECT a,b,c,d,t2.e,f,t3.e80 FROM t181 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-1493 do_execsql_test joinA-$id.120 {94 SELECT a,b,c,d,t2.e,f,t3.e95 FROM t196 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 34102 15 25 - - - 45 35103 16 26 - - - 46 36104 }105 106 # Verified by PG-14107 do_execsql_test joinA-$id.130 {108 SELECT a,b,c,d,t2.e,f,t3.e109 FROM t1110 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-14122 do_execsql_test joinA-$id.140 {123 SELECT a,b,c,d,t2.e,f,t3.e124 FROM t1125 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-14137 do_execsql_test joinA-$id.150 {138 SELECT a,b,c,d,t2.e,f,t3.e139 FROM t1140 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-14152 do_execsql_test joinA-$id.160 {153 SELECT a,b,c,d,t2.e,f,t3.e154 FROM t1155 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-14171 do_execsql_test joinA-$id.170 {172 SELECT a,b,c,d,t2.e,f,t3.e173 FROM t1174 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 34182 15 25 - - - 45 35183 16 26 - - - 46 36184 16 - 26 36 - 46 - 185 19 - 29 39 - 49 - 186 }187 188 # Verified by PG-14189 do_execsql_test joinA-$id.200 {190 SELECT a,b,c,d,t2.e,f,t3.e191 FROM t1192 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 34206 15 25 - - - 45 35207 15 25 35 45 - - - 208 16 26 - - - 46 36209 16 - 26 36 - 46 - 210 18 28 38 48 - - - 211 19 - 29 39 - 49 - 212 }213 214 # Verified by PG-14215 do_execsql_test joinA-$id.201 {216 SELECT a,b,c,d,t2.e,f,t3.e,t1.a217 FROM t1218 FULL JOIN t2 USING(c,d)219 FULL JOIN t3 USING(a,b,f)220 FULL JOIN t4 USING(a,c,d,f)221 WHERE t1.a!=0222 ORDER BY 1 nulls first, 3 nulls first;223 } {224 11 21 31 41 - - - 11225 12 22 32 42 - - - 12226 15 25 35 45 - - - 15227 18 28 38 48 - - - 18228 }229 230 # Verified by PG-14231 do_execsql_test joinA-$id.202 {232 SELECT a,b,c,d,t2.e,f,t3.e,t3.a233 FROM t1234 FULL JOIN t2 USING(c,d)235 FULL JOIN t3 USING(a,b,f)236 FULL JOIN t4 USING(a,c,d,f)237 WHERE t3.a!=0238 ORDER BY 1 nulls first, 3 nulls first;239 } {240 14 24 - - - 44 34 14241 15 25 - - - 45 35 15242 16 26 - - - 46 36 16243 }244 245 # Verified by PG-14246 do_execsql_test joinA-$id.203 {247 SELECT a,b,c,d,t2.e,f,t3.e,t4.a248 FROM t1249 FULL JOIN t2 USING(c,d)250 FULL JOIN t3 USING(a,b,f)251 FULL JOIN t4 USING(a,c,d,f)252 WHERE t4.a!=0253 ORDER BY 1 nulls first, 3 nulls first;254 } {255 11 - 21 31 - 41 - 11256 13 - 23 33 - 43 - 13257 16 - 26 36 - 46 - 16258 19 - 29 39 - 49 - 19259 }260 261 # Verified by PG-14262 do_execsql_test joinA-$id.204 {263 SELECT a,b,c,d,t2.e,f,t3.e264 FROM t1265 FULL JOIN t2 USING(c,d)266 FULL JOIN t3 USING(a,b,f)267 FULL JOIN t4 USING(a,c,d,f)268 WHERE t2.e!=0269 ORDER BY 1 nulls first, 3 nulls first;270 } {271 - - 12 22 32 42 - 272 - - 13 23 33 43 - 273 - - 15 25 35 45 - 274 - - 17 27 37 47 - 275 }276}277finish_test278