gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt3824.test (about)

     1  # 2009 April 24                                                                 
     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  #
    12  # Ticket #3824
    13  #
    14  # When you use an "IS NULL" constraint on a UNIQUE index, the result
    15  # is not necessarily UNIQUE.  Make sure the optimizer does not assume
    16  # uniqueness.
    17  #
    18  # $Id: tkt3824.test,v 1.2 2009/04/24 20:32:31 drh Exp $
    19  
    20  set testdir [file dirname $argv0]
    21  source $testdir/tester.tcl
    22  
    23  proc execsql_status {sql {db db}} {
    24    set result [uplevel $db eval [list $sql]]
    25    if {[db status sort]} {
    26      concat $result sort
    27    } else {
    28      concat $result nosort
    29    }
    30  }
    31  
    32  do_test tkt3824-1.1 {
    33    db eval {
    34      CREATE TABLE t1(a,b);
    35      INSERT INTO t1 VALUES(1,NULL);
    36      INSERT INTO t1 VALUES(9,NULL);
    37      INSERT INTO t1 VALUES(5,NULL);
    38      INSERT INTO t1 VALUES(123,NULL);
    39      INSERT INTO t1 VALUES(-10,NULL);
    40      CREATE UNIQUE INDEX t1b ON t1(b);
    41    }
    42    execsql_status {
    43      SELECT a FROM t1 WHERE b IS NULL ORDER BY a;
    44    }
    45  } {-10 1 5 9 123 sort}
    46  do_test tkt3824-1.2 {
    47    execsql_status {
    48      SELECT a FROM t1 WHERE b IS NULL ORDER BY b, a;
    49    }
    50  } {-10 1 5 9 123 sort}
    51  
    52  do_test tkt3824-2.1 {
    53    db eval {
    54      CREATE TABLE t2(a,b,c);
    55      INSERT INTO t2 VALUES(1,1,NULL);
    56      INSERT INTO t2 VALUES(9,2,NULL);
    57      INSERT INTO t2 VALUES(5,2,NULL);
    58      INSERT INTO t2 VALUES(123,3,NULL);
    59      INSERT INTO t2 VALUES(-10,3,NULL);
    60      CREATE UNIQUE INDEX t2bc ON t2(b,c);
    61    }
    62    execsql_status {
    63      SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY a;
    64    }
    65  } {5 9 sort}
    66  do_test tkt3824-2.2 {
    67    execsql_status {
    68      SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b, a;
    69    }
    70  } {5 9 sort}
    71  do_test tkt3824-2.3 {
    72    lsort [execsql_status {
    73      SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b;
    74    }]
    75  } {5 9 nosort}
    76  
    77  do_test tkt3824-3.1 {
    78    db eval {
    79      CREATE TABLE t3(x,y);
    80      INSERT INTO t3 SELECT a, b FROM t1;
    81      INSERT INTO t3 VALUES(234,567);
    82      CREATE UNIQUE INDEX t3y ON t3(y);
    83      DELETE FROM t3 WHERE y IS NULL;
    84      SELECT * FROM t3;
    85    }
    86  } {234 567}
    87  
    88  do_test tkt3824-4.1 {
    89    db eval {
    90      CREATE TABLE t4(x,y);
    91      INSERT INTO t4 SELECT a, b FROM t1;
    92      INSERT INTO t4 VALUES(234,567);
    93      CREATE UNIQUE INDEX t4y ON t4(y);
    94      UPDATE t4 SET rowid=rowid+100 WHERE y IS NULL;
    95      SELECT rowid, x FROM t4 ORDER BY rowid;
    96    }
    97  } {6 234 101 1 102 9 103 5 104 123 105 -10}
    98  
    99  finish_test