gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/joinH.test (about) 1 # 2022 May 17 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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix joinH 17 18 do_execsql_test 1.0 { 19 CREATE TABLE t1(a INT); 20 CREATE TABLE t2(b INT); 21 INSERT INTO t2(b) VALUES(NULL); 22 } 23 24 db nullvalue NULL 25 26 do_execsql_test 1.1 { 27 SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL); 28 } {NULL} 29 do_execsql_test 1.2 { 30 SELECT a FROM t1 FULL JOIN t2 ON true; 31 } {NULL} 32 do_execsql_test 1.3 { 33 SELECT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL); 34 } {NULL} 35 do_execsql_test 1.4 { 36 SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true; 37 } {NULL} 38 39 #----------------------------------------------------------- 40 41 reset_db 42 do_execsql_test 2.0 { 43 CREATE TABLE r3(x); 44 CREATE TABLE r4(y INTEGER PRIMARY KEY); 45 INSERT INTO r4 VALUES(55); 46 } 47 48 do_execsql_test 2.1 { 49 SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x); 50 } {value!} 51 52 do_execsql_test 2.2 { 53 SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x) WHERE +y=55; 54 } {value!} 55 56 #----------------------------------------------------------- 57 reset_db 58 do_execsql_test 3.1 { 59 CREATE TABLE t0 (c0); 60 CREATE TABLE t1 (c0); 61 CREATE TABLE t2 (c0 , c1 , c2 , UNIQUE (c0), UNIQUE (c2 DESC)); 62 INSERT INTO t2 VALUES ('x', 'y', 'z'); 63 ANALYZE; 64 CREATE VIEW v0(c0) AS SELECT FALSE; 65 } 66 67 do_catchsql_test 3.2 { 68 SELECT * FROM t0 LEFT OUTER JOIN t1 ON v0.c0 INNER JOIN v0 INNER JOIN t2 ON (t2.c2 NOT NULL); 69 } {1 {ON clause references tables to its right}} 70 71 72 finish_test