/ Check-in [eea47933]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Remove a redundant test case (probably a copy/paste error). Add an assert() to where.c to ensure that automatic indexes do not have there output row counts adjusted downward by supplementary constraints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eea47933493c85a85247ad383bd148b06f627d04
User & Date: drh 2014-11-22 12:22:13
Context
2014-11-22
18:50
Deploy heuristics (well-commented) to better estimate how much unindexed terms in the WHERE clause filter the number of output rows from a single table. check-in: 22165994 user: drh tags: trunk
16:14
Unintentional edits mistakenly checked in. Was: Remove an obsolete and incorrect comment from the whereLoopOutputAdjust() routine in the query planner. No changes to working code. Closed-Leaf check-in: e1f893c6 user: drh tags: mistake
12:22
Remove a redundant test case (probably a copy/paste error). Add an assert() to where.c to ensure that automatic indexes do not have there output row counts adjusted downward by supplementary constraints. check-in: eea47933 user: drh tags: trunk
2014-11-20
23:21
Fix a typo in a requirements mark on the abs() SQL function. check-in: b1e6c02f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4302
4303
4304
4305
4306
4307
4308

4309
4310
4311
4312
4313
4314
4315
  LogEst nRow            /* Number of rows in the entire table */
){
  WhereTerm *pTerm, *pX;
  Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf);
  int i, j;
  int nEq = 0;    /* Number of = constraints not within likely()/unlikely() */


  for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){
    if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break;
    if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue;
    if( (pTerm->prereqAll & notAllowed)!=0 ) continue;
    for(j=pLoop->nLTerm-1; j>=0; j--){
      pX = pLoop->aLTerm[j];
      if( pX==0 ) continue;







>







4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
  LogEst nRow            /* Number of rows in the entire table */
){
  WhereTerm *pTerm, *pX;
  Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf);
  int i, j;
  int nEq = 0;    /* Number of = constraints not within likely()/unlikely() */

  assert( (pLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
  for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){
    if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break;
    if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue;
    if( (pTerm->prereqAll & notAllowed)!=0 ) continue;
    for(j=pLoop->nLTerm-1; j>=0; j--){
      pX = pLoop->aLTerm[j];
      if( pX==0 ) continue;

Changes to test/autoindex2.test.

220
221
222
223
224
225
226
227
228
229
230
231
232
233
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
  ORDER BY t1.ptime desc LIMIT 500;
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.

do_execsql_test autoindex2-120 {
  EXPLAIN QUERY PLAN
  SELECT
     t1_id,
     t1.did,
     param2,
     param3,
     t1.ptime,
     t1.trange,
     t1.exmass,
     t1.mass,
     t1.vstatus,
     type,
     subtype,
     t1.deviation,
     t1.formula,
     dparam1,
     reserve1,
     reserve2,
     param4,
     t1.last_operation,
     t1.admin_uuid,
     t1.previous_value,
     t1.job_id,
     client_did, 
     t1.last_t1,
     t1.data_t1,
     t1.previous_date,
     param5,
     param6,
     mgr_uuid
  FROM
     t3,
     t2,
     t1
  WHERE
     t1.ptime > 1393520400
     AND param3<>9001
     AND t3.flg7 = 1
     AND t1.did = t2.did
     AND t2.uid = t3.uid
  ORDER BY t1.ptime desc LIMIT 500;
} {0 0 2 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 0 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}

finish_test







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<

220
221
222
223
224
225
226












































227
  ORDER BY t1.ptime desc LIMIT 500;
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.













































finish_test