test/notnull2.test

   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
  32 do_vmstep_test 1.1.1 {
  33   SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND d IS NULL;
  34 } 100 {}
  35 do_vmstep_test 1.1.2 {
  36   SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND c IS NULL;
  37 } +1000 {}
  38
  39 do_vmstep_test 1.2.1 {
  40   SELECT * FROM ( SELECT * FROM t2 ) WHERE d IS NULL
  41 } 100 {}
  42 do_vmstep_test 1.2.2 {
  43   SELECT * FROM ( SELECT * FROM t2 ) WHERE c IS NULL
  44 } +1000 {}
  45
  46 do_vmstep_test 1.3.1 {
  47   SELECT * FROM t2 WHERE d IS NULL
  48 } 100 {}
  49 do_vmstep_test 1.3.2 {
  50   SELECT * FROM t2 WHERE c IS NULL
  51 } +1000 {}
  52
  53 do_vmstep_test 1.4.1 {
  54   SELECT (d IS NOT NULL) FROM t2 WHERE 0==( d IS NOT NULL )
  55 } 100 {}
  56 do_vmstep_test 1.4.2 {
  57   SELECT * FROM t2 WHERE 0==( c IS NOT NULL )
  58 } +1000 {}
  59
  60 do_vmstep_test 1.5.1 {
  61   SELECT count(*) FROM t2 WHERE EXISTS(
  62     SELECT 1 FROM t1 WHERE t1.a=450 AND t2.d IS NULL
  63   )
  64 } 7000 {0}
  65 do_vmstep_test 1.5.2 {
  66   SELECT count(*) FROM t2 WHERE EXISTS(
  67     SELECT 1 FROM t1 WHERE t1.a=450 AND t2.c IS NULL
  68   )
  69 } +8000 {0}
  70
  71 #-------------------------------------------------------------------------
  72 reset_db
  73 do_execsql_test 2.0 {
  74   CREATE TABLE T1(a INTEGER PRIMARY KEY, b);
  75   CREATE TABLE T3(k, v);
  76 }
  77
  78 do_execsql_test 2.1 {
  79   SELECT * FROM (SELECT a, b FROM t1) LEFT JOIN t3 ON a IS NULL;
  80 }
  81
  82
  83
  84 #-------------------------------------------------------------------------
  85 reset_db
  86 do_execsql_test 3.0 {
  87   CREATE TABLE t0(c0 PRIMARY KEY);
  88   INSERT INTO t0(c0) VALUES (0);
  89 }
  90 do_execsql_test 3.1 {
  91   SELECT * FROM t0 WHERE ((c0 NOT NULL) AND 1) OR (c0 == NULL);
  92 } {0}
  93
  94 # 2021-07-22 https://sqlite.org/forum/forumpost/2078b7edd2
  95 #
  96 reset_db
  97 do_execsql_test 4.0 {
  98   SELECT *, '/'
  99   FROM (
 100       SELECT NULL val FROM (SELECT 1)
 101       UNION ALL
 102       SELECT 'missing' FROM (SELECT 1)
 103   ) a
 104   LEFT JOIN (SELECT 1)
 105       ON a.val IS NULL;
 106 } {{} 1 / missing {} /}
 107 do_execsql_test 4.1 {
 108   CREATE TABLE t1(a INT);
 109   INSERT INTO t1(a) VALUES(1);
 110   CREATE TABLE t2(b INT);
 111   SELECT * FROM (SELECT 3 AS c FROM t1) AS t3 LEFT JOIN t2 ON c IS NULL;
 112 } {3 {}}
 113
 114 # 2024-03-08 https://sqlite.org/forum/forumpost/440f2a2f17
 115 #
 116 reset_db
 117 do_execsql_test 5.0 {
 118   CREATE TABLE t1(a INT NOT NULL);
 119   SELECT a IS NULL, a IS NOT NULL, count(*) FROM t1;
 120 } {1 0 0}
 121
 122 finish_test