gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/notnull2.test (about) 1 # 2021 February 15 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. The 12 # focus of this file is testing optimizations associated with "IS NULL" 13 # and "IS NOT NULL" operators on columns with NOT NULL constraints. 14 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set testprefix notnull2 19 20 do_execsql_test 1.0 { 21 CREATE TABLE t1(a, b); 22 CREATE TABLE t2(c, d NOT NULL); 23 24 WITH x(i) AS ( 25 SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 26 ) 27 INSERT INTO t1 SELECT i, i FROM x; 28 INSERT INTO t2 SELECT * FROM t1; 29 } 30 31 proc do_vmstep_test {tn sql nstep {res {}}} { 32 uplevel [list do_execsql_test $tn.0 $sql $res] 33 34 set vmstep [db status vmstep] 35 if {[string range $nstep 0 0]=="+"} { 36 set body "if {$vmstep<$nstep} { 37 error \"got $vmstep, expected more than [string range $nstep 1 end]\" 38 }" 39 } else { 40 set body "if {$vmstep>$nstep} { 41 error \"got $vmstep, expected less than $nstep\" 42 }" 43 } 44 45 # set name "$tn.vmstep=$vmstep,expect=$nstep" 46 set name "$tn.1" 47 uplevel [list do_test $name $body {}] 48 } 49 50 do_vmstep_test 1.1.1 { 51 SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND d IS NULL; 52 } 100 {} 53 do_vmstep_test 1.1.2 { 54 SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND c IS NULL; 55 } +1000 {} 56 57 do_vmstep_test 1.2.1 { 58 SELECT * FROM ( SELECT * FROM t2 ) WHERE d IS NULL 59 } 100 {} 60 do_vmstep_test 1.2.2 { 61 SELECT * FROM ( SELECT * FROM t2 ) WHERE c IS NULL 62 } +1000 {} 63 64 do_vmstep_test 1.3.1 { 65 SELECT * FROM t2 WHERE d IS NULL 66 } 100 {} 67 do_vmstep_test 1.3.2 { 68 SELECT * FROM t2 WHERE c IS NULL 69 } +1000 {} 70 71 do_vmstep_test 1.4.1 { 72 SELECT (d IS NOT NULL) FROM t2 WHERE 0==( d IS NOT NULL ) 73 } 100 {} 74 do_vmstep_test 1.4.2 { 75 SELECT * FROM t2 WHERE 0==( c IS NOT NULL ) 76 } +1000 {} 77 78 do_vmstep_test 1.5.1 { 79 SELECT count(*) FROM t2 WHERE EXISTS( 80 SELECT t2.d IS NULL FROM t1 WHERE t1.a=450 81 ) 82 } 10000 {1000} 83 do_vmstep_test 1.5.2 { 84 SELECT count(*) FROM t2 WHERE EXISTS( 85 SELECT t2.c IS NULL FROM t1 WHERE t1.a=450 86 ) 87 } +100000 {1000} 88 89 #------------------------------------------------------------------------- 90 reset_db 91 do_execsql_test 2.0 { 92 CREATE TABLE T1(a INTEGER PRIMARY KEY, b); 93 CREATE TABLE T3(k, v); 94 } 95 96 do_execsql_test 2.1 { 97 SELECT * FROM (SELECT a, b FROM t1) LEFT JOIN t3 ON a IS NULL; 98 } 99 100 101 102 #------------------------------------------------------------------------- 103 reset_db 104 do_execsql_test 3.0 { 105 CREATE TABLE t0(c0 PRIMARY KEY); 106 INSERT INTO t0(c0) VALUES (0); 107 } 108 do_execsql_test 3.1 { 109 SELECT * FROM t0 WHERE ((c0 NOT NULL) AND 1) OR (c0 == NULL); 110 } {0} 111 112 # 2021-07-22 https://sqlite.org/forum/forumpost/2078b7edd2 113 # 114 reset_db 115 do_execsql_test 4.0 { 116 SELECT *, '/' 117 FROM ( 118 SELECT NULL val FROM (SELECT 1) 119 UNION ALL 120 SELECT 'missing' FROM (SELECT 1) 121 ) a 122 LEFT JOIN (SELECT 1) 123 ON a.val IS NULL; 124 } {{} 1 / missing {} /} 125 do_execsql_test 4.1 { 126 CREATE TABLE t1(a INT); 127 INSERT INTO t1(a) VALUES(1); 128 CREATE TABLE t2(b INT); 129 SELECT * FROM (SELECT 3 AS c FROM t1) AS t3 LEFT JOIN t2 ON c IS NULL; 130 } {3 {}} 131 132 finish_test