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 18do_execsql_test join7-1.1 { 19 CREATE TABLE t1(a int,b int); 20 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 21 CREATE INDEX t1a ON t1(a); 22 CREATE TABLE t2(c int,d int); 23 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 24 CREATE INDEX t2c ON t2(c); 25 SELECT quote(b), quote(d) FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 26} { 27 NULL 55 28 2 NULL 29 3 33 30 4 44 31} 32do_execsql_test join7-1.2 { 33 SELECT quote(a), quote(c) FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 34} { 35 NULL 5 36 1 NULL 37 1 3 38 1 4 39} 40do_execsql_test join7-1.3 { 41 SELECT quote(a), quote(b), quote(c), quote(d) 42 FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 43} { 44 NULL NULL 5 55 45 1 2 NULL NULL 46 1 3 3 33 47 1 4 4 44 48} 49do_execsql_test join7-1.4 { 50 SELECT quote(a), quote(b), quote(c), quote(d) 51 FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 52} { 53 NULL NULL 5 55 54 1 3 3 33 55 1 4 4 44 56} 57do_execsql_test join7-1.5 { 58 SELECT quote(a), quote(b), quote(c), quote(d) 59 FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b; 60} { 61 1 2 NULL NULL 62 1 3 3 33 63 1 4 4 44 64} 65 66do_execsql_test join7-2.1 { 67 SELECT quote(a), quote(b), quote(c), quote(d) 68 FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b; 69} { 70 NULL NULL 5 55 71 1 2 NULL NULL 72 1 3 3 33 73 1 4 4 44 74} 75do_execsql_test join7-2.2 { 76 SELECT quote(a), quote(b), quote(c), quote(d) 77 FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b; 78} { 79 1 2 NULL NULL 80 1 3 3 33 81 1 4 4 44 82} 83do_execsql_test join7-2.3 { 84 SELECT quote(a), quote(b), quote(c), quote(d) 85 FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d; 86} { 87 NULL NULL 3 33 88 NULL NULL 4 44 89 NULL NULL 5 55 90} 91do_execsql_test join7-2.4 { 92 SELECT quote(a), quote(b), quote(c), quote(d) 93 FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d; 94} { 95 NULL NULL 3 33 96 NULL NULL 4 44 97 NULL NULL 5 55 98 1 2 NULL NULL 99 1 3 NULL NULL 100 1 4 NULL NULL 101} 102 103 104 105finish_test 106