mirror of
https://github.com/sqlite/sqlite.git
synced 2026-10-26 21:59:27 +03:00
07f9fbd52f
and that term of the WHERE clause is not used for indexing, assume that the term reduces the number of output rows by half. This is one proposed fix for ticket [e8b674241947eb3ba4] FossilOrigin-Name: 5c243eec7bcccda3f97c20c2f8a44e2811807615cf0ef378bb548f0791c55964
115 lines
3.2 KiB
Plaintext
115 lines
3.2 KiB
Plaintext
# 2018-07-26
|
|
#
|
|
# 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.
|
|
#
|
|
#***********************************************************************
|
|
# This file implements regression tests for SQLite library. The
|
|
# focus of this file is testing the WHERE-clause constant propagation
|
|
# optimization.
|
|
#
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
set ::testprefix whereL
|
|
|
|
do_execsql_test 100 {
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b, c, d, e);
|
|
CREATE TABLE t2(a INT PRIMARY KEY, f, g, h, i);
|
|
CREATE TABLE t3(a INT PRIMARY KEY, j, k, l, m);
|
|
CREATE VIEW v4 AS SELECT * FROM t2 UNION ALL SELECT * FROM t3;
|
|
}
|
|
do_eqp_test 110 {
|
|
SELECT * FROM t1, v4 WHERE t1.a=?1 AND v4.a=t1.a;
|
|
} {
|
|
QUERY PLAN
|
|
|--MATERIALIZE xxxxxx
|
|
| `--COMPOUND QUERY
|
|
| |--LEFT-MOST SUBQUERY
|
|
| | `--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
|
|
| `--UNION ALL
|
|
| `--SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?)
|
|
|--SCAN SUBQUERY xxxxxx
|
|
`--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
|
|
}
|
|
|
|
# The scan of the t1 table goes first since that enables the ORDER BY
|
|
# sort to be omitted. This would not be possible without constant
|
|
# propagation because without it the t1 table would depend on t3.
|
|
#
|
|
do_eqp_test 120 {
|
|
SELECT * FROM t1, t2, t3
|
|
WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=5
|
|
ORDER BY t1.a;
|
|
} {
|
|
QUERY PLAN
|
|
|--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
|
|
|--SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (a=?)
|
|
`--SEARCH TABLE t3 USING AUTOMATIC PARTIAL COVERING INDEX (j=?)
|
|
}
|
|
|
|
# Constant propagation in the face of collating sequences:
|
|
#
|
|
do_execsql_test 200 {
|
|
CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
|
|
CREATE INDEX c3x ON c3(x);
|
|
INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');
|
|
SELECT * FROM c3 WHERE x=y AND y=z AND z='abc';
|
|
} {ABC ABC abc}
|
|
|
|
# If the constants are blindly propagated, as shown in the following
|
|
# query, the wrong answer results:
|
|
#
|
|
do_execsql_test 201 {
|
|
SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
|
|
} {}
|
|
|
|
# Constant propagation caused an incorrect answer in the following
|
|
# query. (Reported by Bentley system on 2018-08-09.)
|
|
#
|
|
do_execsql_test 300 {
|
|
CREATE TABLE A(id INTEGER PRIMARY KEY, label TEXT);
|
|
CREATE TABLE B(id INTEGER PRIMARY KEY, label TEXT, Aid INTEGER);
|
|
CREATE TABLE C(
|
|
id INTEGER PRIMARY KEY,
|
|
xx INTEGER NOT NULL,
|
|
yy INTEGER,
|
|
zz INTEGER
|
|
);
|
|
CREATE UNIQUE INDEX x2 ON C(yy);
|
|
CREATE UNIQUE INDEX x4 ON C(yy, zz);
|
|
INSERT INTO A(id) VALUES(1);
|
|
INSERT INTO B(id) VALUES(2);
|
|
INSERT INTO C(id,xx,yy,zz) VALUES(99,50,1,2);
|
|
SELECT 1
|
|
FROM A,
|
|
(SELECT id,xx,yy,zz FROM C) subq,
|
|
B
|
|
WHERE A.id='1'
|
|
AND A.id=subq.yy
|
|
AND B.id=subq.zz;
|
|
} {1}
|
|
do_execsql_test 301 {
|
|
SELECT 1
|
|
FROM A,
|
|
(SELECT id,xx,yy,zz FROM C) subq,
|
|
B
|
|
WHERE A.id=1
|
|
AND A.id=subq.yy
|
|
AND B.id=subq.zz;
|
|
} {1}
|
|
do_execsql_test 302 {
|
|
SELECT 1
|
|
FROM A,
|
|
(SELECT id,yy,zz FROM C) subq,
|
|
B
|
|
WHERE A.id='1'
|
|
AND A.id=subq.yy
|
|
AND B.id=subq.zz;
|
|
} {1}
|
|
|
|
finish_test
|