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