| # 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}} |
| |
| # 2015-06-18 |
| # Ticket [https://www.sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70] |
| # |
| do_execsql_test 6.0 { |
| DROP TABLE IF EXISTS t1; |
| CREATE TABLE t1(i int, x, y, z); |
| INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4); |
| DROP TABLE IF EXISTS t2; |
| CREATE TABLE t2(i int, bool char); |
| INSERT INTO t2 VALUES(1,'T'), (2,'F'); |
| SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T'; |
| SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND bool='T'; |
| } {4 4} |
| |
| # 2015-06-20 |
| # Crash discovered by AFL |
| # |
| do_execsql_test 7.0 { |
| DROP TABLE IF EXISTS t1; |
| CREATE TABLE t1(a, b, PRIMARY KEY(a,b)); |
| INSERT INTO t1 VALUES(9,1),(1,2); |
| DROP TABLE IF EXISTS t2; |
| CREATE TABLE t2(x, y, PRIMARY KEY(x,y)); |
| INSERT INTO t2 VALUES(3,3),(4,4); |
| SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2; |
| } {1 3 1 4 9 3 9 4} |
| do_execsql_test 7.1 { |
| SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2; |
| } {1 3 1 4 9 3 9 4} |
| do_execsql_test 7.2 { |
| SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2; |
| } {1 3 1 4 9 3 9 4} |
| do_execsql_test 7.3 { |
| SELECT coalesce(a,a), x FROM t1, t2 ORDER BY 1, 2; |
| } {1 3 1 4 9 3 9 4} |
| |
| |
| finish_test |