3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
12 # Test cases for query planning decisions and the unlikely() and
13 # likelihood() functions.
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 do_execsql_test whereG-1.0 {
19 CREATE TABLE composer(
20 cid INTEGER PRIMARY KEY,
24 aid INTEGER PRIMARY KEY,
28 tid INTEGER PRIMARY KEY,
29 cid INTEGER REFERENCES composer,
30 aid INTEGER REFERENCES album,
33 CREATE INDEX track_i1 ON track(cid);
34 CREATE INDEX track_i2 ON track(aid);
35 INSERT INTO composer VALUES(1, 'W. A. Mozart');
36 INSERT INTO composer VALUES(2, 'Beethoven');
37 INSERT INTO composer VALUES(3, 'Thomas Tallis');
38 INSERT INTO composer VALUES(4, 'Joseph Hayden');
39 INSERT INTO composer VALUES(5, 'Thomas Weelkes');
40 INSERT INTO composer VALUES(6, 'J. S. Bach');
41 INSERT INTO composer VALUES(7, 'Orlando Gibbons');
42 INSERT INTO composer VALUES(8, 'Josquin des Prés');
43 INSERT INTO composer VALUES(9, 'Byrd');
44 INSERT INTO composer VALUES(10, 'Francis Poulenc');
45 INSERT INTO composer VALUES(11, 'Mendelsshon');
46 INSERT INTO composer VALUES(12, 'Zoltán Kodály');
47 INSERT INTO composer VALUES(13, 'Handel');
48 INSERT INTO album VALUES(100, 'Kodály: Missa Brevis');
49 INSERT INTO album VALUES(101, 'Messiah');
50 INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65');
51 INSERT INTO album VALUES(103, 'The complete English anthems');
52 INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232');
53 INSERT INTO track VALUES(10005, 12, 100, 'Sanctus');
54 INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei');
55 INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs');
56 INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death');
57 INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei');
58 INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me');
59 INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus');
60 INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis');
62 do_eqp_test whereG-1.1 {
64 FROM album, composer, track
65 WHERE unlikely(cname LIKE '%bach%')
66 AND composer.cid=track.cid
67 AND album.aid=track.aid;
68 } {/.*composer.*track.*album.*/}
69 do_execsql_test whereG-1.2 {
71 FROM album, composer, track
72 WHERE unlikely(cname LIKE '%bach%')
73 AND composer.cid=track.cid
74 AND album.aid=track.aid;
75 } {{Mass in B Minor, BWV 232}}
77 do_eqp_test whereG-1.3 {
79 FROM album, composer, track
80 WHERE likelihood(cname LIKE '%bach%', 0.5)
81 AND composer.cid=track.cid
82 AND album.aid=track.aid;
83 } {/.*track.*composer.*album.*/}
84 do_execsql_test whereG-1.4 {
86 FROM album, composer, track
87 WHERE likelihood(cname LIKE '%bach%', 0.5)
88 AND composer.cid=track.cid
89 AND album.aid=track.aid;
90 } {{Mass in B Minor, BWV 232}}
92 do_eqp_test whereG-1.5 {
94 FROM album, composer, track
95 WHERE cname LIKE '%bach%'
96 AND composer.cid=track.cid
97 AND album.aid=track.aid;
98 } {/.*track.*composer.*album.*/}
99 do_execsql_test whereG-1.6 {
100 SELECT DISTINCT aname
101 FROM album, composer, track
102 WHERE cname LIKE '%bach%'
103 AND composer.cid=track.cid
104 AND album.aid=track.aid;
105 } {{Mass in B Minor, BWV 232}}
107 do_eqp_test whereG-1.7 {
108 SELECT DISTINCT aname
109 FROM album, composer, track
110 WHERE cname LIKE '%bach%'
111 AND unlikely(composer.cid=track.cid)
112 AND unlikely(album.aid=track.aid);
113 } {/.*track.*composer.*album.*/}
114 do_execsql_test whereG-1.8 {
115 SELECT DISTINCT aname
116 FROM album, composer, track
117 WHERE cname LIKE '%bach%'
118 AND unlikely(composer.cid=track.cid)
119 AND unlikely(album.aid=track.aid);
120 } {{Mass in B Minor, BWV 232}}
124 SELECT DISTINCT aname
125 FROM album, composer, track
126 WHERE likelihood(cname LIKE '%bach%', -0.01)
127 AND composer.cid=track.cid
128 AND album.aid=track.aid;
130 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
133 SELECT DISTINCT aname
134 FROM album, composer, track
135 WHERE likelihood(cname LIKE '%bach%', 1.01)
136 AND composer.cid=track.cid
137 AND album.aid=track.aid;
139 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
142 SELECT DISTINCT aname
143 FROM album, composer, track
144 WHERE likelihood(cname LIKE '%bach%', track.cid)
145 AND composer.cid=track.cid
146 AND album.aid=track.aid;
148 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
150 # Commuting a term of the WHERE clause should not change the query plan
152 do_execsql_test whereG-3.0 {
153 CREATE TABLE a(a1 PRIMARY KEY, a2);
154 CREATE TABLE b(b1 PRIMARY KEY, b2);
156 do_eqp_test whereG-3.1 {
157 SELECT * FROM a, b WHERE b1=a1 AND a2=5;
158 } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
159 do_eqp_test whereG-3.2 {
160 SELECT * FROM a, b WHERE a1=b1 AND a2=5;
161 } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
162 do_eqp_test whereG-3.3 {
163 SELECT * FROM a, b WHERE a2=5 AND b1=a1;
164 } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
165 do_eqp_test whereG-3.4 {
166 SELECT * FROM a, b WHERE a2=5 AND a1=b1;
167 } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}