xref: /sqlite-3.40.0/test/tkt3824.test (revision ac6de304) 
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
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23proc 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
32do_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}
46do_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
52do_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}
66do_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}
71do_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
77do_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
88do_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
99finish_test
100