Return to tkt3824.test CVS log

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

Annotation of embedaddon/sqlite3/test/tkt3824.test, revision 1.1.1.1

1.1       misho       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