Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Run test files analyze5.test, analyze6.test and analyze7.test as part of src4.test. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8eb90b66474992a790e5fa93375e3121 |
| User & Date: | dan 2013-06-25 11:58:21 |
Context
|
2013-06-25
| ||
| 14:47 | Have the planner always consider the PK index, even when it does not serve to satisfy any contraints or ordering requirements. check-in: 95933bb7ca user: dan tags: trunk | |
| 11:58 | Run test files analyze5.test, analyze6.test and analyze7.test as part of src4.test. check-in: 8eb90b6647 user: dan tags: trunk | |
|
2013-06-24
| ||
| 20:06 | Fix another bug in sqlite_stat3 related code. check-in: bad9060b5b user: dan tags: trunk | |
Changes
Changes to test/analyze5.test.
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
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
|
}
set testprefix analyze5
proc eqp {sql {db db}} {
uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
}
unset -nocomplain i t u v w x y z
do_test analyze5-1.0 {
db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)}
for {set i 0} {$i < 1000} {incr i} {
set y [expr {$i>=25 && $i<=50}]
set z [expr {($i>=400) + ($i>=700) + ($i>=875)}]
................................................................................
CREATE INDEX t1u ON t1(u); -- text
CREATE INDEX t1v ON t1(v); -- mixed case text
CREATE INDEX t1w ON t1(w); -- integers 0, 1, 2 and a few NULLs
CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs
CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s
CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 3
ANALYZE;
SELECT sample FROM sqlite_stat3 WHERE idx='t1u' ORDER BY nlt;
}
} {alpha bravo charlie delta}
do_test analyze5-1.1 {
db eval {SELECT DISTINCT lower(sample) FROM sqlite_stat3 WHERE idx='t1v'
ORDER BY 1}
} {alpha bravo charlie delta}
do_test analyze5-1.2 {
db eval {SELECT idx, count(*) FROM sqlite_stat3 GROUP BY 1 ORDER BY 1}
} {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4}
# Verify that range queries generate the correct row count estimates
#
foreach {testid where index rows} {
1 {z>=0 AND z<=0} t1z 400
2 {z>=1 AND z<=1} t1z 300
3 {z>=2 AND z<=2} t1z 175
|
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
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
..
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
|
}
set testprefix analyze5
proc eqp {sql {db db}} {
uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
}
# This command is registered as a user-defined function with the database
# handle. The blob passed as the only argument is a text-value encoded
# using the sqlite4 key-encoding with collation sequence BINARY. This
# command extracts and returns the text value.
#
proc decode {blob} {
binary scan $blob c* vars
binary format c* [lrange $vars 1 end-1]
}
db func decode decode
unset -nocomplain i t u v w x y z
do_test analyze5-1.0 {
db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)}
for {set i 0} {$i < 1000} {incr i} {
set y [expr {$i>=25 && $i<=50}]
set z [expr {($i>=400) + ($i>=700) + ($i>=875)}]
................................................................................
CREATE INDEX t1u ON t1(u); -- text
CREATE INDEX t1v ON t1(v); -- mixed case text
CREATE INDEX t1w ON t1(w); -- integers 0, 1, 2 and a few NULLs
CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs
CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s
CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 3
ANALYZE;
SELECT decode(sample) FROM sqlite_stat3 WHERE idx='t1u' ORDER BY nlt;
}
} {alpha bravo charlie delta}
do_test analyze5-1.1 {
db eval {SELECT DISTINCT decode(sample) FROM sqlite_stat3 WHERE idx='t1v'
ORDER BY 1}
} {alpha bravo charlie delta}
do_test analyze5-1.2 {
db eval {SELECT idx, count(*) FROM sqlite_stat3 GROUP BY 1 ORDER BY 1}
} {t1 24 t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4}
# Verify that range queries generate the correct row count estimates
#
foreach {testid where index rows} {
1 {z>=0 AND z<=0} t1z 400
2 {z>=1 AND z<=1} t1z 300
3 {z>=2 AND z<=2} t1z 175
|
Changes to test/analyze6.test.
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
..
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
|
# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {0 0 1 {SCAN TABLE cat (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
eqp {SELECT count(*) FROM cat, ev WHERE x=y}
} {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
#
do_test analyze6-2.1 {
................................................................................
CREATE INDEX t201z ON t201(z);
ANALYZE;
}
eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
do_test analyze6-2.2 {
eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}}
do_test analyze6-2.3 {
eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze6-2.4 {
execsql {
INSERT INTO t201 VALUES(1,2,3);
ANALYZE t201;
}
eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
do_test analyze6-2.5 {
eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}}
do_test analyze6-2.6 {
eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze6-2.7 {
execsql {
INSERT INTO t201 VALUES(4,5,7);
INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201;
INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201;
INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201;
ANALYZE t201;
}
eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
do_test analyze6-2.8 {
eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}}
do_test analyze6-2.9 {
eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
finish_test
|
|
|
|
|
|
|
|
|
|
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
..
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
|
# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {0 0 1 {SCAN TABLE cat (~16 rows)} 0 1 0 {SEARCH TABLE ev USING INDEX evy (y=?) (~32 rows)}}
# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
eqp {SELECT count(*) FROM cat, ev WHERE x=y}
} {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING INDEX evy (y=?) (~32 rows)}}
# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
#
do_test analyze6-2.1 {
................................................................................
CREATE INDEX t201z ON t201(z);
ANALYZE;
}
eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
do_test analyze6-2.2 {
eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_t201_unique2 (y=?) (~1 rows)}}
do_test analyze6-2.3 {
eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING PRIMARY KEY (x=?) (~1 rows)}}
do_test analyze6-2.4 {
execsql {
INSERT INTO t201 VALUES(1,2,3);
ANALYZE t201;
}
eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
do_test analyze6-2.5 {
eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_t201_unique2 (y=?) (~1 rows)}}
do_test analyze6-2.6 {
eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING PRIMARY KEY (x=?) (~1 rows)}}
do_test analyze6-2.7 {
execsql {
INSERT INTO t201 VALUES(4,5,7);
INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201;
INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201;
INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201;
ANALYZE t201;
}
eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
do_test analyze6-2.8 {
eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_t201_unique2 (y=?) (~1 rows)}}
do_test analyze6-2.9 {
eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING PRIMARY KEY (x=?) (~1 rows)}}
finish_test
|
Changes to test/permutations.test.
137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
lsm1.test lsm2.test lsm3.test lsm4.test lsm5.test csr1.test ckpt1.test mc1.test fts5expr1.test fts5query1.test fts5rnd1.test fts5create.test fts5snippet.test analyze.test analyze3.test analyze4.test auth.test auth2.test auth3.test auth4.test aggerror.test attach.test autoindex1.test badutf.test between.test bigrow.test |
| > |
137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
lsm1.test lsm2.test lsm3.test lsm4.test lsm5.test csr1.test ckpt1.test mc1.test fts5expr1.test fts5query1.test fts5rnd1.test fts5create.test fts5snippet.test analyze.test analyze3.test analyze4.test analyze5.test analyze6.test analyze7.test auth.test auth2.test auth3.test auth4.test aggerror.test attach.test autoindex1.test badutf.test between.test bigrow.test |