xref: /sqlite-3.40.0/test/intarray.test (revision 60ce5d31) 
1# 2009 November 10
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.
12#
13# This file implements tests for the "intarray" object implemented
14# in test_intarray.c.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20ifcapable !vtab {
21  return
22}
23
24do_test intarray-1.0 {
25  db eval {
26    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
27  }
28  for {set i 1} {$i<=999} {incr i} {
29    set b [format {x%03d} $i]
30    db eval {INSERT INTO t1(a,b) VALUES($i,$b)}
31  }
32  db eval {
33    CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
34    INSERT INTO t2 SELECT * FROM t1;
35    SELECT b FROM t1 WHERE a IN (12,34,56,78) ORDER BY a
36  }
37} {x012 x034 x056 x078}
38
39do_test intarray-1.1 {
40  set ia1 [sqlite3_intarray_create db ia1]
41  set ia2 [sqlite3_intarray_create db ia2]
42  set ia3 [sqlite3_intarray_create db ia3]
43  set ia4 [sqlite3_intarray_create db ia4]
44  db eval {
45    SELECT type, name FROM temp.sqlite_master
46     ORDER BY name
47  }
48} {table ia1 table ia2 table ia3 table ia4}
49
50# Verify the inability to DROP and recreate an intarray virtual table.
51do_test intarray-1.1b {
52  db eval {DROP TABLE ia1}
53  set rc [catch {sqlite3_intarray_create db ia1} msg]
54  lappend rc $msg
55} {1 SQLITE_MISUSE}
56
57do_test intarray-1.2 {
58  db eval {
59    SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
60  }
61} {}
62
63do_test intarray-1.3 {
64  sqlite3_intarray_bind $ia3 45 123 678
65  db eval {
66    SELECT b FROM t1 WHERE a IN ia3 ORDER BY a
67  }
68} {x045 x123 x678}
69
70do_test intarray-1.4 {
71  db eval {
72    SELECT count(b) FROM t1 WHERE a NOT IN ia3 ORDER BY a
73  }
74} {996}
75
76#explain {SELECT b FROM t1 WHERE a NOT IN ia3}
77
78do_test intarray-1.5 {
79  set cmd sqlite3_intarray_bind
80  lappend cmd $ia1
81  for {set i 1} {$i<=999} {incr i} {
82    lappend cmd $i
83    lappend cmd [expr {$i+1000}]
84    lappend cmd [expr {$i+2000}]
85  }
86  eval $cmd
87  db eval {
88    REPLACE INTO t1 SELECT * FROM t2;
89    DELETE FROM t1 WHERE a NOT IN ia1;
90    SELECT count(*) FROM t1;
91  }
92} {999}
93
94do_test intarray-1.6 {
95  db eval {
96    DELETE FROM t1 WHERE a IN ia1;
97    SELECT count(*) FROM t1;
98  }
99} {0}
100
101do_test intarray-2.1 {
102  db eval {
103    CREATE TEMP TABLE t3(p,q);
104    INSERT INTO t3 SELECT * FROM t2;
105    SELECT count(*) FROM t3 WHERE p IN ia1;
106  }
107} {999}
108
109do_test intarray-2.2 {
110  set ia5 [sqlite3_intarray_create db ia5]
111  db eval {
112    SELECT count(*) FROM t3 WHERE p IN ia1;
113  }
114} {999}
115
116finish_test
117