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 t4(id INTEGER PRIMARY KEY, x TEXT); 21 CREATE TABLE t5(id INTEGER PRIMARY KEY, y TEXT); 22 CREATE TABLE t6(id INTEGER PRIMARY KEY, z INT); 23 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); 24 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), 25 (5,'blue'); 26 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); 27 } 28} { 29 reset_db 30 db nullvalue - 31 do_execsql_test join9-$id.setup $schema {} 32 do_execsql_test join9-$id.10 { 33 SELECT *, t4.id, t5.id, t6.id 34 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 35 ORDER BY id; 36 } { 37 2 alice orange - 2 2 - 38 4 bob green 444 4 4 4 39 6 cindy - - 6 - - 40 8 dave - - 8 - - 41 } 42 do_execsql_test join9-$id.20 { 43 SELECT *, t4.id, t5.id, t6.id 44 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 45 ORDER BY id; 46 } { 47 0 - - 1000 - - 0 48 3 - yellow 333 - 3 3 49 4 bob green 444 4 4 4 50 5 - blue 555 - 5 5 51 9 - - 999 - - 9 52 } 53 do_execsql_test join9-$id.30 { 54 SELECT *, t4.id, t5.id, t6.id 55 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 56 ORDER BY id; 57 } { 58 0 - - 1000 - - 0 59 1 - red - - 1 - 60 2 alice orange - 2 2 - 61 3 - yellow 333 - 3 3 62 4 bob green 444 4 4 4 63 5 - blue 555 - 5 5 64 6 cindy - - 6 - - 65 8 dave - - 8 - - 66 9 - - 999 - - 9 67 } 68} 69finish_test 70