mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-16 10:18:47 +00:00
389 lines
11 KiB
Plaintext
389 lines
11 KiB
Plaintext
# 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 a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
|
|
do_eqp_test whereG-3.2 {
|
|
SELECT * FROM a, b WHERE a1=b1 AND a2=5;
|
|
} {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
|
|
do_eqp_test whereG-3.3 {
|
|
SELECT * FROM a, b WHERE a2=5 AND b1=a1;
|
|
} {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
|
|
do_eqp_test whereG-3.4 {
|
|
SELECT * FROM a, b WHERE a2=5 AND a1=b1;
|
|
} {/.*SCAN a.*SEARCH 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>?
|
|
} {SEARCH t1 USING INDEX i1 (a>?)}
|
|
do_eqp_test 5.1.3 {
|
|
SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
|
|
} {SCAN t1}
|
|
do_eqp_test 5.1.4 {
|
|
SELECT * FROM t1 WHERE likely(a>?)
|
|
} {SCAN 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)
|
|
} {SEARCH t1 USING INDEX i1 (ANY(a) AND b>?)}
|
|
do_eqp_test 5.2.3 {
|
|
SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
|
|
} {SCAN t1}
|
|
do_eqp_test 5.2.4 {
|
|
SELECT * FROM t1 WHERE likely(b>?)
|
|
} {SCAN t1}
|
|
|
|
ifcapable stat4 {
|
|
do_eqp_test 5.3.1.stat4 {
|
|
SELECT * FROM t1 WHERE a=?
|
|
} {SCAN t1}
|
|
} else {
|
|
do_eqp_test 5.3.1 {
|
|
SELECT * FROM t1 WHERE a=?
|
|
} {SEARCH t1 USING INDEX i1}
|
|
}
|
|
do_eqp_test 5.3.2 {
|
|
SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
|
|
} {SCAN t1}
|
|
do_eqp_test 5.3.3 {
|
|
SELECT * FROM t1 WHERE likely(a=?)
|
|
} {SCAN 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}
|
|
|
|
# 2019-08-22
|
|
# Ticket https://www.sqlite.org/src/info/7e07a3dbf5a8cd26
|
|
#
|
|
do_execsql_test 8.1 {
|
|
DROP TABLE IF EXISTS t0;
|
|
CREATE TABLE t0 (c0);
|
|
INSERT INTO t0(c0) VALUES ('a');
|
|
SELECT LIKELY(t0.rowid) <= '0' FROM t0;
|
|
} {1}
|
|
do_execsql_test 8.2 {
|
|
SELECT * FROM t0 WHERE LIKELY(t0.rowid) <= '0';
|
|
} {a}
|
|
do_execsql_test 8.3 {
|
|
SELECT (t0.rowid) <= '0' FROM t0;
|
|
} {0}
|
|
do_execsql_test 8.4 {
|
|
SELECT * FROM t0 WHERE (t0.rowid) <= '0';
|
|
} {}
|
|
do_execsql_test 8.5 {
|
|
SELECT unlikely(t0.rowid) <= '0', likelihood(t0.rowid,0.5) <= '0' FROM t0;
|
|
} {1 1}
|
|
do_execsql_test 8.6 {
|
|
SELECT * FROM t0 WHERE unlikely(t0.rowid) <= '0';
|
|
} {a}
|
|
do_execsql_test 8.7 {
|
|
SELECT * FROM t0 WHERE likelihood(t0.rowid, 0.5) <= '0';
|
|
} {a}
|
|
do_execsql_test 8.8 {
|
|
SELECT unlikely(t0.rowid <= '0'),
|
|
likely(t0.rowid <= '0'),
|
|
likelihood(t0.rowid <= '0',0.5)
|
|
FROM t0;
|
|
} {0 0 0}
|
|
do_execsql_test 8.9 {
|
|
SELECT * FROM t0 WHERE unlikely(t0.rowid <= '0');
|
|
} {}
|
|
do_execsql_test 8.10 {
|
|
SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5);
|
|
} {}
|
|
|
|
# 2019-12-31: assertion fault discovered by Yongheng's fuzzer.
|
|
# Harmless memIsValid() due to the code generators failure to
|
|
# release the registers used by OP_ResultRow.
|
|
#
|
|
do_execsql_test 9.10 {
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(a, b FLOAT);
|
|
INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
|
|
SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
|
|
} {NULL '' 'X'}
|
|
|
|
# 2020-06-14: assert() changed back into testcase()
|
|
# ticket 9fb26d37cefaba40
|
|
#
|
|
reset_db
|
|
do_execsql_test 10.1 {
|
|
CREATE TABLE a(b TEXT); INSERT INTO a VALUES(0),(4),(9);
|
|
CREATE TABLE c(d NUM);
|
|
CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c;
|
|
SELECT g = g FROM f GROUP BY h;
|
|
} {1}
|
|
|
|
reset_db
|
|
do_execsql_test 11.0 {
|
|
CREATE TABLE t1(x PRIMARY KEY, y);
|
|
INSERT INTO t1 VALUES('AAA', 'BBB');
|
|
|
|
CREATE TABLE t2(z);
|
|
INSERT INTO t2 VALUES('t2');
|
|
|
|
CREATE TABLE t3(x PRIMARY KEY, y);
|
|
INSERT INTO t3 VALUES('AAA', 'AAA');
|
|
}
|
|
|
|
do_execsql_test 11.1.1 {
|
|
SELECT * FROM t1 JOIN t2 ON unlikely(x=y) AND y='AAA'
|
|
}
|
|
do_execsql_test 11.1.2 {
|
|
SELECT * FROM t1 JOIN t2 ON likely(x=y) AND y='AAA'
|
|
}
|
|
do_execsql_test 11.1.3 {
|
|
SELECT * FROM t1 JOIN t2 ON x=y AND y='AAA'
|
|
}
|
|
|
|
do_execsql_test 11.2.1 {
|
|
SELECT * FROM t3 JOIN t2 ON unlikely(x=y) AND y='AAA'
|
|
} {AAA AAA t2}
|
|
do_execsql_test 11.2.2 {
|
|
SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
|
|
} {AAA AAA t2}
|
|
do_execsql_test 11.2.3 {
|
|
SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
|
|
} {AAA AAA t2}
|
|
|
|
# 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a
|
|
# Affinity problem when a likely() function is used as a column in
|
|
# an index.
|
|
#
|
|
reset_db
|
|
do_execsql_test 12.0 {
|
|
CREATE TABLE t1(a REAL);
|
|
INSERT INTO t1(a) VALUES(123);
|
|
CREATE INDEX t1x1 ON t1(likely(a));
|
|
SELECT typeof(likely(a)) FROM t1 NOT INDEXED;
|
|
SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1;
|
|
} {real real}
|
|
do_execsql_test 12.1 {
|
|
CREATE INDEX t1x2 ON t1(abs(a));
|
|
SELECT typeof(abs(a)) FROM t1 NOT INDEXED;
|
|
SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2;
|
|
} {real real}
|
|
|
|
|
|
finish_test
|