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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
8eb90b66474992a790e5fa93375e3121 |
User & Date: | dan 2013-06-25 11:58:21.781 |
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 | } 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)}] | > > > > > > > > > > > | 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 | } 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)}] |
︙ | ︙ | |||
51 52 53 54 55 56 57 | 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; | | | | | 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 | 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 | # 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} | | | | | | | | | | 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 | # 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 { execsql { CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z); 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 | 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 | | > | 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 |
︙ | ︙ |