Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem in where.c with using the stat4 sample data of an index on a WITHOUT ROWID table. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | stat4-without-rowid |
Files: | files | file ages | folders |
SHA1: |
053a210e3169732c58f84cb54c9b6f6d |
User & Date: | dan 2014-06-30 18:02:20.849 |
Context
2014-06-30
| ||
18:57 | Fix for ticket [b2fa5424e6fcb15]: Better define the format of the sqlite_stat4 file for WITHOUT ROWID tables and make sure the ANALYZE command generates a file in the appropriate format. Use the sqlite_stat4 data to enable the use of WHERE terms that cover all indexed columns plus some prefix of columns in the primary key. (check-in: bc2de8095f user: drh tags: trunk) | |
18:02 | Fix a problem in where.c with using the stat4 sample data of an index on a WITHOUT ROWID table. (Closed-Leaf check-in: 053a210e31 user: dan tags: stat4-without-rowid) | |
17:07 | Fix the STAT4 information for WITHOUT ROWID tables. (check-in: 5d8628fdff user: drh tags: stat4-without-rowid) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2197 2198 2199 2200 2201 2202 2203 | UnpackedRecord *pRec = pBuilder->pRec; u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); | | | | 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 | UnpackedRecord *pRec = pBuilder->pRec; u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); assert( nEq<=p->nColumn ); assert( p->aSample!=0 ); assert( p->nSample>0 ); assert( pBuilder->nRecValid<nEq ); /* If values are not available for all fields of the index to the left ** of this one, no estimate can be made. Return SQLITE_NOTFOUND. */ if( pBuilder->nRecValid<(nEq-1) ){ return SQLITE_NOTFOUND; } /* This is an optimization only. The call to sqlite3Stat4ProbeSetValue() ** below would return the same value. */ if( nEq>=p->nColumn ){ *pnRow = 1; return SQLITE_OK; } aff = p->pTable->aCol[p->aiColumn[nEq-1]].affinity; rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk); pBuilder->pRec = pRec; |
︙ | ︙ |
Changes to test/analyze9.test.
︙ | ︙ | |||
948 949 950 951 952 953 954 955 956 | for {set i 0} {$i<16} {incr i} { set val "$i $i $i $i" do_execsql_test 20.3.$i { SELECT count(*) FROM sqlite_stat4 WHERE lrange(test_decode(sample), 0, 3)=$val } {1} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 | for {set i 0} {$i<16} {incr i} { set val "$i $i $i $i" do_execsql_test 20.3.$i { SELECT count(*) FROM sqlite_stat4 WHERE lrange(test_decode(sample), 0, 3)=$val } {1} } #------------------------------------------------------------------------- # reset_db do_execsql_test 21.0 { CREATE TABLE t2(a, b); CREATE INDEX i2 ON t2(a); } do_test 21.1 { for {set i 1} {$i < 100} {incr i} { execsql { INSERT INTO t2 VALUES(CASE WHEN $i < 80 THEN 'one' ELSE 'two' END, $i) } } execsql ANALYZE } {} # Condition (a='one') matches 80% of the table. (rowid<10) reduces this to # 10%, but (rowid<50) only reduces it to 50%. So in the first case below # the index is used. In the second, it is not. # do_eqp_test 21.2 { SELECT * FROM t2 WHERE a='one' AND rowid < 10 } {/*USING INDEX i2 (a=? AND rowid<?)*/} do_eqp_test 21.3 { SELECT * FROM t2 WHERE a='one' AND rowid < 50 } {/*USING INTEGER PRIMARY KEY*/} #------------------------------------------------------------------------- # reset_db do_execsql_test 22.0 { CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; } do_execsql_test 22.1 { WITH r(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<=100 ) INSERT INTO t3 SELECT CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END, /* Column "a" */ x, /* Column "b" */ CASE WHEN (x<51) THEN 'one' ELSE 'two' END, /* Column "c" */ x /* Column "d" */ FROM r; CREATE INDEX i3 ON t3(c); CREATE INDEX i4 ON t3(d); ANALYZE; } # Expression (c='one' AND a='B') matches 5 table rows. But (c='one' AND a=A') # matches 45. Expression (d<?) matches 20. Neither index is a covering index. # # Therefore, with stat4 data, SQLite prefers (c='one' AND a='B') over (d<20), # and (d<20) over (c='one' AND a='A'). foreach {tn where res} { 1 "c='one' AND a='B' AND d < 20" {/*INDEX i3 (c=? AND a=?)*/} 2 "c='one' AND a='A' AND d < 20" {/*INDEX i4 (d<?)*/} } { do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res } finish_test |