Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the EXPLAIN QUERY PLAN output for row value range constaints that use an index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rowvalue |
Files: | files | file ages | folders |
SHA1: |
bb60651163553c5e46bf7b2805490570 |
User & Date: | dan 2016-08-08 18:42:08.741 |
Context
2016-08-08
| ||
20:15 | Fix some cases involving row values and virtual tables. (check-in: 156a41f30a user: dan tags: rowvalue) | |
18:42 | Fix the EXPLAIN QUERY PLAN output for row value range constaints that use an index. (check-in: bb60651163 user: dan tags: rowvalue) | |
16:52 | Merge trunk changes with this branch. (check-in: 0e927a7e02 user: dan tags: rowvalue) | |
Changes
Changes to src/wherecode.c.
︙ | ︙ | |||
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | ** that actually generate the bulk of the WHERE loop code. The original where.c ** file retains the code that does query planning and analysis. */ #include "sqliteInt.h" #include "whereInt.h" #ifndef SQLITE_OMIT_EXPLAIN /* ** This routine is a helper for explainIndexRange() below ** ** pStr holds the text of an expression that we are building up one term ** at a time. This routine adds a new term to the end of the expression. ** Terms are separated by AND so add the "AND" text for second and subsequent ** terms only. */ static void explainAppendTerm( StrAccum *pStr, /* The text expression being built */ | > > > > > > > > > > > > > | | > > > | > > > > | > | > | | > > > > | < < < < < | < < | 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 | ** that actually generate the bulk of the WHERE loop code. The original where.c ** file retains the code that does query planning and analysis. */ #include "sqliteInt.h" #include "whereInt.h" #ifndef SQLITE_OMIT_EXPLAIN /* ** Return the name of the i-th column of the pIdx index. */ static const char *explainIndexColumnName(Index *pIdx, int i){ i = pIdx->aiColumn[i]; if( i==XN_EXPR ) return "<expr>"; if( i==XN_ROWID ) return "rowid"; return pIdx->pTable->aCol[i].zName; } /* ** This routine is a helper for explainIndexRange() below ** ** pStr holds the text of an expression that we are building up one term ** at a time. This routine adds a new term to the end of the expression. ** Terms are separated by AND so add the "AND" text for second and subsequent ** terms only. */ static void explainAppendTerm( StrAccum *pStr, /* The text expression being built */ Index *pIdx, /* Index to read column names from */ int nTerm, /* Number of terms */ int iTerm, /* Zero-based index of first term. */ int bAnd, /* Non-zero to append " AND " */ const char *zOp /* Name of the operator */ ){ int i; assert( nTerm>=1 ); if( bAnd ) sqlite3StrAccumAppend(pStr, " AND ", 5); if( nTerm>1 ) sqlite3StrAccumAppend(pStr, "(", 1); for(i=0; i<nTerm; i++){ if( i ) sqlite3StrAccumAppend(pStr, ",", 1); sqlite3StrAccumAppendAll(pStr, explainIndexColumnName(pIdx, iTerm+i)); } if( nTerm>1 ) sqlite3StrAccumAppend(pStr, ")", 1); sqlite3StrAccumAppend(pStr, zOp, 1); if( nTerm>1 ) sqlite3StrAccumAppend(pStr, "(", 1); for(i=0; i<nTerm; i++){ if( i ) sqlite3StrAccumAppend(pStr, ",", 1); sqlite3StrAccumAppend(pStr, "?", 1); } if( nTerm>1 ) sqlite3StrAccumAppend(pStr, ")", 1); } /* ** Argument pLevel describes a strategy for scanning table pTab. This ** function appends text to pStr that describes the subset of table ** rows scanned by the strategy in the form of an SQL expression. ** |
︙ | ︙ | |||
77 78 79 80 81 82 83 | const char *z = explainIndexColumnName(pIndex, i); if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5); sqlite3XPrintf(pStr, i>=nSkip ? "%s=?" : "ANY(%s)", z); } j = i; if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ | < | > < | | 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | const char *z = explainIndexColumnName(pIndex, i); if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5); sqlite3XPrintf(pStr, i>=nSkip ? "%s=?" : "ANY(%s)", z); } j = i; if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ explainAppendTerm(pStr, pIndex, pLoop->u.btree.nBtm, j, i, ">"); i = 1; } if( pLoop->wsFlags&WHERE_TOP_LIMIT ){ explainAppendTerm(pStr, pIndex, pLoop->u.btree.nTop, j, i, "<"); } sqlite3StrAccumAppend(pStr, ")", 1); } /* ** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN ** command, or if either SQLITE_DEBUG or SQLITE_ENABLE_STMT_SCANSTATUS was |
︙ | ︙ |
Changes to test/rowvalue4.test.
︙ | ︙ | |||
201 202 203 204 205 206 207 | do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } { | | | 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd ((c,d)>(?,?))} } do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } } #------------------------------------------------------------------------ |
︙ | ︙ | |||
234 235 236 237 238 239 240 241 | } { 0 0 0 {SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)} 0 0 0 {EXECUTE LIST SUBQUERY 1} 1 0 0 {SCAN TABLE d1} 0 0 0 {EXECUTE LIST SUBQUERY 2} 2 0 0 {SCAN TABLE d1} } | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 | } { 0 0 0 {SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)} 0 0 0 {EXECUTE LIST SUBQUERY 1} 1 0 0 {SCAN TABLE d1} 0 0 0 {EXECUTE LIST SUBQUERY 2} 2 0 0 {SCAN TABLE d1} } do_execsql_test 6.0 { CREATE TABLE e1(a, b, c, d, e); CREATE INDEX e1ab ON e1(a, b); CREATE INDEX e1cde ON e1(c, d, e); } do_eqp_test 6.1 { SELECT * FROM e1 WHERE (a, b) > (?, ?) } { 0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)>(?,?))} } do_eqp_test 6.2 { SELECT * FROM e1 WHERE (a, b) < (?, ?) } { 0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)<(?,?))} } do_eqp_test 6.3 { SELECT * FROM e1 WHERE c = ? AND (d, e) > (?, ?) } { 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?))} } do_eqp_test 6.4 { SELECT * FROM e1 WHERE c = ? AND (d, e) < (?, ?) } { 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)<(?,?))} } do_eqp_test 6.5 { SELECT * FROM e1 WHERE (d, e) BETWEEN (?, ?) AND (?, ?) AND c = ? } { 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?) AND (d,e)<(?,?))} } finish_test |