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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to tkt-80ba201079.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test