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