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