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. 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18do_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} {} 62do_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.*/} 69do_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}} 76 77do_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.*/} 84do_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}} 91 92do_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.*/} 99do_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}} 106 107do_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.*/} 114do_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}} 121 122do_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}} 131do_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}} 140do_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}} 149 150# Commuting a term of the WHERE clause should not change the query plan 151# 152do_execsql_test whereG-3.0 { 153 CREATE TABLE a(a1 PRIMARY KEY, a2); 154 CREATE TABLE b(b1 PRIMARY KEY, b2); 155} {} 156do_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=..*/} 159do_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=..*/} 162do_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=..*/} 165do_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=..*/} 168 169 170finish_test 171