xref: /sqlite-3.40.0/test/notnull2.test (revision 348e002e) 
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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18set testprefix notnull2
19
20do_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
31proc 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
50do_vmstep_test 1.1.1 {
51  SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND d IS NULL;
52} 100 {}
53do_vmstep_test 1.1.2 {
54  SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND c IS NULL;
55} +1000 {}
56
57do_vmstep_test 1.2.1 {
58  SELECT * FROM ( SELECT * FROM t2 ) WHERE d IS NULL
59} 100 {}
60do_vmstep_test 1.2.2 {
61  SELECT * FROM ( SELECT * FROM t2 ) WHERE c IS NULL
62} +1000 {}
63
64do_vmstep_test 1.3.1 {
65  SELECT * FROM t2 WHERE d IS NULL
66} 100 {}
67do_vmstep_test 1.3.2 {
68  SELECT * FROM t2 WHERE c IS NULL
69} +1000 {}
70
71do_vmstep_test 1.4.1 {
72  SELECT (d IS NOT NULL) FROM t2 WHERE 0==( d IS NOT NULL )
73} 100 {}
74do_vmstep_test 1.4.2 {
75  SELECT * FROM t2 WHERE 0==( c IS NOT NULL )
76} +1000 {}
77
78do_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}
83do_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#-------------------------------------------------------------------------
90reset_db
91do_execsql_test 2.0 {
92  CREATE TABLE T1(a INTEGER PRIMARY KEY, b);
93  CREATE TABLE T3(k, v);
94}
95
96do_execsql_test 2.1 {
97  SELECT * FROM (SELECT a, b FROM t1) LEFT JOIN t3 ON a IS NULL;
98}
99
100
101
102#-------------------------------------------------------------------------
103reset_db
104do_execsql_test 3.0 {
105  CREATE TABLE t0(c0 PRIMARY KEY);
106  INSERT INTO t0(c0) VALUES (0);
107}
108do_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#
114reset_db
115do_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 {} /}
125do_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
132finish_test
133