| blob | 1b6a861855bbafc0d37d5e4f4d46bc45e70ebe57 |
1 # 2013-09-05
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 # 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,
21 cname TEXT
22 );
23 CREATE TABLE album(
24 aid INTEGER PRIMARY KEY,
25 aname TEXT
26 );
27 CREATE TABLE track(
28 tid INTEGER PRIMARY KEY,
29 cid INTEGER REFERENCES composer,
30 aid INTEGER REFERENCES album,
31 title TEXT
32 );
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');
61 } {}
62 do_eqp_test whereG-1.1 {
63 SELECT DISTINCT aname
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 {
70 SELECT DISTINCT aname
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 {
78 SELECT DISTINCT aname
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 {
85 SELECT DISTINCT aname
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 {
93 SELECT DISTINCT aname
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}}
122 do_test whereG-2.1 {
123 catchsql {
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;
129 }
130 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
131 do_test whereG-2.2 {
132 catchsql {
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;
138 }
139 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
140 do_test whereG-2.3 {
141 catchsql {
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;
147 }
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
151 #
152 do_execsql_test whereG-3.0 {
153 CREATE TABLE a(a1 PRIMARY KEY, a2);
154 CREATE TABLE b(b1 PRIMARY KEY, b2);
155 } {}
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=..*/}
170 finish_test