| 1 |
# 2014-06-17 |
| 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 |
# This file implements regression tests for SQLite library. The |
| 13 |
# focus of this script is testing automatic index creation logic, |
| 14 |
# and specifically that an automatic index will not be created that |
| 15 |
# shadows a declared index. |
| 16 |
# |
| 17 |
|
| 18 |
set testdir [file dirname $argv0] |
| 19 |
source $testdir/tester.tcl |
| 20 |
set testprefix autoindex3 |
| 21 |
|
| 22 |
# The t1b and t2d indexes are not very selective. It used to be that |
| 23 |
# the autoindex mechanism would create automatic indexes on t1(b) or |
| 24 |
# t2(d), make assumptions that they were reasonably selective, and use |
| 25 |
# them instead of t1b or t2d. But that would be cheating, because the |
| 26 |
# automatic index cannot be any more selective than the real index. |
| 27 |
# |
| 28 |
# This test verifies that the cheat is no longer allowed. |
| 29 |
# |
| 30 |
do_execsql_test autoindex3-100 { |
| 31 |
CREATE TABLE t1(a,b,x); |
| 32 |
CREATE TABLE t2(c,d,y); |
| 33 |
CREATE INDEX t1b ON t1(b); |
| 34 |
CREATE INDEX t2d ON t2(d); |
| 35 |
ANALYZE sqlite_master; |
| 36 |
INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500'); |
| 37 |
INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500'); |
| 38 |
ANALYZE sqlite_master; |
| 39 |
EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b; |
| 40 |
} {~/AUTO/} |
| 41 |
|
| 42 |
# Automatic indexes can still be used if existing indexes do not |
| 43 |
# participate in == constraints. |
| 44 |
# |
| 45 |
do_execsql_test autoindex3-110 { |
| 46 |
EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y; |
| 47 |
} {/AUTO/} |
| 48 |
do_execsql_test autoindex3-120 { |
| 49 |
EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y; |
| 50 |
} {/AUTO/} |
| 51 |
do_execsql_test autoindex3-130 { |
| 52 |
EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y; |
| 53 |
} {/AUTO/} |
| 54 |
do_execsql_test autoindex3-140 { |
| 55 |
EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y; |
| 56 |
} {/AUTO/} |
| 57 |
|
| 58 |
reset_db |
| 59 |
do_execsql_test 210 { |
| 60 |
CREATE TABLE v(b, d, e); |
| 61 |
CREATE TABLE u(a, b, c); |
| 62 |
ANALYZE sqlite_master; |
| 63 |
INSERT INTO "sqlite_stat1" VALUES('u','uab','40000 400 1'); |
| 64 |
INSERT INTO "sqlite_stat1" VALUES('v','vbde','40000 400 1 1'); |
| 65 |
INSERT INTO "sqlite_stat1" VALUES('v','ve','40000 21'); |
| 66 |
|
| 67 |
CREATE INDEX uab on u(a, b); |
| 68 |
CREATE INDEX ve on v(e); |
| 69 |
CREATE INDEX vbde on v(b,d,e); |
| 70 |
|
| 71 |
DROP TABLE IF EXISTS sqlite_stat4; |
| 72 |
ANALYZE sqlite_master; |
| 73 |
} |
| 74 |
|
| 75 |
# At one point, SQLite was using the inferior plan: |
| 76 |
# |
| 77 |
# 0|0|1|SEARCH v USING INDEX ve (e>?) |
| 78 |
# 0|1|0|SEARCH u USING COVERING INDEX uab (ANY(a) AND b=?) |
| 79 |
# |
| 80 |
# on the basis that the real index "uab" must be better than the automatic |
| 81 |
# index. This is not right - a skip-scan is not necessarily better than an |
| 82 |
# automatic index scan. |
| 83 |
# |
| 84 |
do_eqp_test 220 { |
| 85 |
select count(*) from u, v where u.b = v.b and v.e > 34; |
| 86 |
} { |
| 87 |
QUERY PLAN |
| 88 |
|--SEARCH v USING INDEX ve (e>?) |
| 89 |
|--BLOOM FILTER ON u (b=?) |
| 90 |
`--SEARCH u USING AUTOMATIC COVERING INDEX (b=?) |
| 91 |
} |
| 92 |
|
| 93 |
# 2024-05-27 |
| 94 |
# ticket https://sqlite.org/src/tktview/8ff324e120 |
| 95 |
# forum post https://sqlite.org/forum/forumpost/b21c2101a559be0a |
| 96 |
# |
| 97 |
# If an index with STAT1 data indicates that a column is not very |
| 98 |
# selective, then do not attempt to create an automatic index on |
| 99 |
# that column. |
| 100 |
# |
| 101 |
reset_db |
| 102 |
do_execsql_test 300 { |
| 103 |
CREATE TABLE t1(id INTEGER PRIMARY KEY); |
| 104 |
CREATE TABLE t2(cid INT, pid INT, rx INT, PRIMARY KEY(cid, pid, rx)); |
| 105 |
CREATE INDEX x1 ON t2(pid, rx); |
| 106 |
ANALYZE sqlite_schema; |
| 107 |
REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES |
| 108 |
('t2', 'x1', '500000 250 250'), |
| 109 |
('t2','sqlite_autoindex_t2_1','500000 1 1 1'); |
| 110 |
ANALYZE sqlite_schema; |
| 111 |
} |
| 112 |
do_eqp_test 310 { |
| 113 |
WITH RECURSIVE children(id) AS ( |
| 114 |
SELECT cid FROM t2 WHERE pid = ?1 AND rx = ?2 |
| 115 |
UNION |
| 116 |
SELECT cid FROM t2 JOIN children ON t2.pid = children.id AND rx = ?2 |
| 117 |
) SELECT count(id) FROM children; |
| 118 |
} { |
| 119 |
QUERY PLAN |
| 120 |
|--CO-ROUTINE children |
| 121 |
| |--SETUP |
| 122 |
| | `--SEARCH t2 USING INDEX x1 (pid=? AND rx=?) |
| 123 |
| `--RECURSIVE STEP |
| 124 |
| |--SCAN children |
| 125 |
| `--SEARCH t2 USING INDEX x1 (pid=? AND rx=?) |
| 126 |
`--SCAN children |
| 127 |
} |
| 128 |
|
| 129 |
finish_test |