From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Thu, 16 Oct 2014 22:51:35 -0400 Subject: Imported Upstream version 3.2.0 --- test/whereG.test | 233 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 233 insertions(+) create mode 100644 test/whereG.test (limited to 'test/whereG.test') diff --git a/test/whereG.test b/test/whereG.test new file mode 100644 index 0000000..c2c54db --- /dev/null +++ b/test/whereG.test @@ -0,0 +1,233 @@ +# 2013-09-05 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Test cases for query planning decisions and the likely(), unlikely(), and +# likelihood() functions. + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix whereG + +do_execsql_test whereG-1.0 { + CREATE TABLE composer( + cid INTEGER PRIMARY KEY, + cname TEXT + ); + CREATE TABLE album( + aid INTEGER PRIMARY KEY, + aname TEXT + ); + CREATE TABLE track( + tid INTEGER PRIMARY KEY, + cid INTEGER REFERENCES composer, + aid INTEGER REFERENCES album, + title TEXT + ); + CREATE INDEX track_i1 ON track(cid); + CREATE INDEX track_i2 ON track(aid); + INSERT INTO composer VALUES(1, 'W. A. Mozart'); + INSERT INTO composer VALUES(2, 'Beethoven'); + INSERT INTO composer VALUES(3, 'Thomas Tallis'); + INSERT INTO composer VALUES(4, 'Joseph Hayden'); + INSERT INTO composer VALUES(5, 'Thomas Weelkes'); + INSERT INTO composer VALUES(6, 'J. S. Bach'); + INSERT INTO composer VALUES(7, 'Orlando Gibbons'); + INSERT INTO composer VALUES(8, 'Josquin des Prés'); + INSERT INTO composer VALUES(9, 'Byrd'); + INSERT INTO composer VALUES(10, 'Francis Poulenc'); + INSERT INTO composer VALUES(11, 'Mendelsshon'); + INSERT INTO composer VALUES(12, 'Zoltán Kodály'); + INSERT INTO composer VALUES(13, 'Handel'); + INSERT INTO album VALUES(100, 'Kodály: Missa Brevis'); + INSERT INTO album VALUES(101, 'Messiah'); + INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65'); + INSERT INTO album VALUES(103, 'The complete English anthems'); + INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232'); + INSERT INTO track VALUES(10005, 12, 100, 'Sanctus'); + INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei'); + INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs'); + INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death'); + INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei'); + INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me'); + INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus'); + INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis'); +} {} +do_eqp_test whereG-1.1 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE unlikely(cname LIKE '%bach%') + AND composer.cid=track.cid + AND album.aid=track.aid; +} {/.*composer.*track.*album.*/} +do_execsql_test whereG-1.2 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE unlikely(cname LIKE '%bach%') + AND composer.cid=track.cid + AND album.aid=track.aid; +} {{Mass in B Minor, BWV 232}} + +do_eqp_test whereG-1.3 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE likelihood(cname LIKE '%bach%', 0.5) + AND composer.cid=track.cid + AND album.aid=track.aid; +} {/.*track.*composer.*album.*/} +do_execsql_test whereG-1.4 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE likelihood(cname LIKE '%bach%', 0.5) + AND composer.cid=track.cid + AND album.aid=track.aid; +} {{Mass in B Minor, BWV 232}} + +do_eqp_test whereG-1.5 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE cname LIKE '%bach%' + AND composer.cid=track.cid + AND album.aid=track.aid; +} {/.*track.*(composer.*album|album.*composer).*/} +do_execsql_test whereG-1.6 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE cname LIKE '%bach%' + AND composer.cid=track.cid + AND album.aid=track.aid; +} {{Mass in B Minor, BWV 232}} + +do_eqp_test whereG-1.7 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE cname LIKE '%bach%' + AND unlikely(composer.cid=track.cid) + AND unlikely(album.aid=track.aid); +} {/.*track.*(composer.*album|album.*composer).*/} +do_execsql_test whereG-1.8 { + SELECT DISTINCT aname + FROM album, composer, track + WHERE cname LIKE '%bach%' + AND unlikely(composer.cid=track.cid) + AND unlikely(album.aid=track.aid); +} {{Mass in B Minor, BWV 232}} + +do_test whereG-2.1 { + catchsql { + SELECT DISTINCT aname + FROM album, composer, track + WHERE likelihood(cname LIKE '%bach%', -0.01) + AND composer.cid=track.cid + AND album.aid=track.aid; + } +} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} +do_test whereG-2.2 { + catchsql { + SELECT DISTINCT aname + FROM album, composer, track + WHERE likelihood(cname LIKE '%bach%', 1.01) + AND composer.cid=track.cid + AND album.aid=track.aid; + } +} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} +do_test whereG-2.3 { + catchsql { + SELECT DISTINCT aname + FROM album, composer, track + WHERE likelihood(cname LIKE '%bach%', track.cid) + AND composer.cid=track.cid + AND album.aid=track.aid; + } +} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} + +# Commuting a term of the WHERE clause should not change the query plan +# +do_execsql_test whereG-3.0 { + CREATE TABLE a(a1 PRIMARY KEY, a2); + CREATE TABLE b(b1 PRIMARY KEY, b2); +} {} +do_eqp_test whereG-3.1 { + SELECT * FROM a, b WHERE b1=a1 AND a2=5; +} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} +do_eqp_test whereG-3.2 { + SELECT * FROM a, b WHERE a1=b1 AND a2=5; +} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} +do_eqp_test whereG-3.3 { + SELECT * FROM a, b WHERE a2=5 AND b1=a1; +} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} +do_eqp_test whereG-3.4 { + SELECT * FROM a, b WHERE a2=5 AND a1=b1; +} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} + +# Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]: +# Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy +# where an OP_Copy was needed. +# +do_execsql_test whereG-4.0 { + CREATE TABLE t4(x); + INSERT INTO t4 VALUES('right'),('wrong'); + SELECT DISTINCT x + FROM (SELECT x FROM t4 GROUP BY x) + WHERE x='right' + ORDER BY x; +} {right} + +#------------------------------------------------------------------------- +# Test that likelihood() specifications on indexed terms are taken into +# account by various forms of loops. +# +# 5.1.*: open ended range scans +# 5.2.*: skip-scans +# +reset_db + +do_execsql_test 5.1 { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a, b); +} +do_eqp_test 5.1.2 { + SELECT * FROM t1 WHERE a>? +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} +do_eqp_test 5.1.3 { + SELECT * FROM t1 WHERE likelihood(a>?, 0.9) +} {0 0 0 {SCAN TABLE t1}} +do_eqp_test 5.1.4 { + SELECT * FROM t1 WHERE likely(a>?) +} {0 0 0 {SCAN TABLE t1}} + +do_test 5.2 { + for {set i 0} {$i < 100} {incr i} { + execsql { INSERT INTO t1 VALUES('abc', $i, $i); } + } + execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; } + execsql { ANALYZE } +} {} +do_eqp_test 5.2.2 { + SELECT * FROM t1 WHERE likelihood(b>?, 0.01) +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}} +do_eqp_test 5.2.3 { + SELECT * FROM t1 WHERE likelihood(b>?, 0.9) +} {0 0 0 {SCAN TABLE t1}} +do_eqp_test 5.2.4 { + SELECT * FROM t1 WHERE likely(b>?) +} {0 0 0 {SCAN TABLE t1}} + +do_eqp_test 5.3.1 { + SELECT * FROM t1 WHERE a=? +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} +do_eqp_test 5.3.2 { + SELECT * FROM t1 WHERE likelihood(a=?, 0.9) +} {0 0 0 {SCAN TABLE t1}} +do_eqp_test 5.3.3 { + SELECT * FROM t1 WHERE likely(a=?) +} {0 0 0 {SCAN TABLE t1}} + +finish_test -- cgit v1.2.3