Return to tkt3824.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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