wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2010 December 6
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. Specifically,
12 # it tests that ticket [80ba201079ea608071d22a57856b940ea3ac53ce] is
13 # resolved. That ticket is about an incorrect result that appears when
14 # an index is added. The root cause is that a constant is being used
15 # without initialization when the OR optimization applies in the WHERE clause.
16 #
17  
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set ::testprefix tkt-80ba2
21  
22 do_test tkt-80ba2-100 {
23 db eval {
24 CREATE TABLE t1(a);
25 INSERT INTO t1 VALUES('A');
26 CREATE TABLE t2(b);
27 INSERT INTO t2 VALUES('B');
28 CREATE TABLE t3(c);
29 INSERT INTO t3 VALUES('C');
30 SELECT * FROM t1, t2
31 WHERE (a='A' AND b='X')
32 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
33 }
34 } {A B}
35 do_test tkt-80ba2-101 {
36 db eval {
37 CREATE INDEX i1 ON t1(a);
38 SELECT * FROM t1, t2
39 WHERE (a='A' AND b='X')
40 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
41 }
42 } {A B}
43 do_test tkt-80ba2-102 {
44 optimization_control db factor-constants 0
45 db cache flush
46 db eval {
47 SELECT * FROM t1, t2
48 WHERE (a='A' AND b='X')
49 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
50 }
51 } {A B}
52 optimization_control db all 1
53  
54 # Verify that the optimization_control command is actually working
55 #
56 do_test tkt-80ba2-150 {
57 optimization_control db factor-constants 1
58 db cache flush
59 set x1 [db eval {EXPLAIN
60 SELECT * FROM t1, t2
61 WHERE (a='A' AND b='X')
62 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
63 optimization_control db factor-constants 0
64 db cache flush
65 set x2 [db eval {EXPLAIN
66 SELECT * FROM t1, t2
67 WHERE (a='A' AND b='X')
68 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
69  
70 expr {$x1==$x2}
71 } {0}
72  
73 do_test tkt-80ba2-200 {
74 db eval {
75 CREATE TABLE entry_types (
76 id integer primary key,
77 name text
78 );
79 INSERT INTO "entry_types" VALUES(100,'cli_command');
80 INSERT INTO "entry_types" VALUES(300,'object_change');
81 CREATE TABLE object_changes (
82 change_id integer primary key,
83 system_id int,
84 obj_id int,
85 obj_context text,
86 change_type int,
87 command_id int
88 );
89 INSERT INTO "object_changes" VALUES(1551,1,114608,'exported_pools',1,2114);
90 INSERT INTO "object_changes" VALUES(2048,1,114608,'exported_pools',2,2319);
91 CREATE TABLE timeline (
92 rowid integer primary key,
93 timestamp text,
94 system_id int,
95 entry_type int,
96 entry_id int
97 );
98 INSERT INTO "timeline" VALUES(6735,'2010-11-21 17:08:27.000',1,300,2048);
99 INSERT INTO "timeline" VALUES(6825,'2010-11-21 17:09:21.000',1,300,2114);
100 SELECT entry_type,
101 entry_types.name,
102 entry_id
103 FROM timeline JOIN entry_types ON entry_type = entry_types.id
104 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
105 OR (entry_types.name = 'object_change'
106 AND entry_id IN (SELECT change_id
107 FROM object_changes
108 WHERE obj_context = 'exported_pools'));
109 }
110 } {300 object_change 2048}
111 do_test tkt-80ba2-201 {
112 db eval {
113 CREATE INDEX timeline_entry_id_idx on timeline(entry_id);
114 SELECT entry_type,
115 entry_types.name,
116 entry_id
117 FROM timeline JOIN entry_types ON entry_type = entry_types.id
118 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
119 OR (entry_types.name = 'object_change'
120 AND entry_id IN (SELECT change_id
121 FROM object_changes
122 WHERE obj_context = 'exported_pools'));
123 }
124 } {300 object_change 2048}
125 do_test tkt-80ba2-202 {
126 optimization_control db factor-constants 0
127 db cache flush
128 db eval {
129 SELECT entry_type,
130 entry_types.name,
131 entry_id
132 FROM timeline JOIN entry_types ON entry_type = entry_types.id
133 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
134 OR (entry_types.name = 'object_change'
135 AND entry_id IN (SELECT change_id
136 FROM object_changes
137 WHERE obj_context = 'exported_pools'));
138 }
139 } {300 object_change 2048}
140  
141 #-------------------------------------------------------------------------
142 #
143  
144 drop_all_tables
145 do_execsql_test 301 {
146 CREATE TABLE t1(a, b, c);
147 CREATE INDEX i1 ON t1(a);
148 CREATE INDEX i2 ON t1(b);
149 CREATE TABLE t2(d, e);
150  
151 INSERT INTO t1 VALUES('A', 'B', 'C');
152 INSERT INTO t2 VALUES('D', 'E');
153 }
154  
155 do_execsql_test 302 {
156 SELECT * FROM t1, t2 WHERE
157 (a='A' AND d='E') OR
158 (b='B' AND c IN ('C', 'D', 'E'))
159 } {A B C D E}
160  
161 do_execsql_test 303 {
162 SELECT * FROM t1, t2 WHERE
163 (a='A' AND d='E') OR
164 (b='B' AND c IN (SELECT c FROM t1))
165 } {A B C D E}
166  
167 do_execsql_test 304 {
168 SELECT * FROM t1, t2 WHERE
169 (a='A' AND d='E') OR
170 (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'))
171 } {A B C D E}
172  
173 do_execsql_test 305 {
174 SELECT * FROM t1, t2 WHERE
175 (b='B' AND c IN ('C', 'D', 'E')) OR
176 (a='A' AND d='E')
177 } {A B C D E}
178  
179 do_execsql_test 306 {
180 SELECT * FROM t1, t2 WHERE
181 (b='B' AND c IN (SELECT c FROM t1)) OR
182 (a='A' AND d='E')
183 } {A B C D E}
184  
185 do_execsql_test 307 {
186 SELECT * FROM t1, t2 WHERE
187 (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')) OR
188 (a='A' AND d='E')
189 } {A B C D E}
190  
191 finish_test