/ Check-in [aa66b864]
Login

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

Overview
Comment:Fix a bug in the transitive_closure virtual table that NGQP exposes. Update between.test module to avoid using sqlite_query_plan.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:aa66b864204b4b8717d845499132b987eb8ef924
User & Date: drh 2013-06-03 18:50:15
Context
2013-06-03
19:01
Resolve ambiguities in test cases in collate2.test. check-in: dcbbcb2e user: drh tags: nextgen-query-plan-exp
18:50
Fix a bug in the transitive_closure virtual table that NGQP exposes. Update between.test module to avoid using sqlite_query_plan. check-in: aa66b864 user: drh tags: nextgen-query-plan-exp
18:14
Fix EXPLAIN QUERY PLAN row counts for the rtree virtual table tests. check-in: 15328987 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/closure.c.

   824    824     sqlite3_index_info *pIdxInfo    /* Information about the query */
   825    825   ){
   826    826     int iPlan = 0;
   827    827     int i;
   828    828     int idx = 1;
   829    829     const struct sqlite3_index_constraint *pConstraint;
   830    830     closure_vtab *pVtab = (closure_vtab*)pTab;
          831  +  double rCost = 10000000.0;
   831    832   
   832    833     pConstraint = pIdxInfo->aConstraint;
   833    834     for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
   834    835       if( pConstraint->usable==0 ) continue;
   835    836       if( (iPlan & 1)==0 
   836    837        && pConstraint->iColumn==CLOSURE_COL_ROOT
   837    838        && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
   838    839       ){
   839    840         iPlan |= 1;
   840    841         pIdxInfo->aConstraintUsage[i].argvIndex = 1;
   841    842         pIdxInfo->aConstraintUsage[i].omit = 1;
          843  +      rCost /= 100.0;
   842    844       }
   843    845       if( (iPlan & 0x0000f0)==0
   844    846        && pConstraint->iColumn==CLOSURE_COL_DEPTH
   845    847        && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
   846    848              || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE
   847    849              || pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ)
   848    850       ){
   849    851         iPlan |= idx<<4;
   850    852         pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
   851    853         if( pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT ) iPlan |= 0x000002;
          854  +      rCost /= 5.0;
   852    855       }
   853    856       if( (iPlan & 0x000f00)==0
   854    857        && pConstraint->iColumn==CLOSURE_COL_TABLENAME
   855    858        && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
   856    859       ){
   857    860         iPlan |= idx<<8;
   858    861         pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
   859    862         pIdxInfo->aConstraintUsage[i].omit = 1;
          863  +      rCost /= 5.0;
   860    864       }
   861    865       if( (iPlan & 0x00f000)==0
   862    866        && pConstraint->iColumn==CLOSURE_COL_IDCOLUMN
   863    867        && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
   864    868       ){
   865    869         iPlan |= idx<<12;
   866    870         pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
................................................................................
   887    891     pIdxInfo->idxNum = iPlan;
   888    892     if( pIdxInfo->nOrderBy==1
   889    893      && pIdxInfo->aOrderBy[0].iColumn==CLOSURE_COL_ID
   890    894      && pIdxInfo->aOrderBy[0].desc==0
   891    895     ){
   892    896       pIdxInfo->orderByConsumed = 1;
   893    897     }
   894         -  pIdxInfo->estimatedCost = (double)10000;
          898  +  pIdxInfo->estimatedCost = rCost;
   895    899      
   896    900     return SQLITE_OK;
   897    901   }
   898    902   
   899    903   /*
   900    904   ** A virtual table module that implements the "approximate_match".
   901    905   */

Changes to test/between.test.

    51     51   # is done.  Then it appends the ::sqlite_query_plan variable.
    52     52   #
    53     53   proc queryplan {sql} {
    54     54     set ::sqlite_sort_count 0
    55     55     set data [execsql $sql]
    56     56     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    57     57     lappend data $x
    58         -  return [concat $data $::sqlite_query_plan]
           58  +  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
           59  +  # puts eqp=$eqp
           60  +  foreach {a b c x} $eqp {
           61  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
           62  +        $x all as tab idx]} {
           63  +      lappend data $tab $idx
           64  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
           65  +      lappend data $tab *
           66  +    }
           67  +  }
           68  +  return $data   
    59     69   }
    60     70   
    61     71   do_test between-1.1.1 {
    62     72     queryplan {
    63     73       SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
    64     74     }
    65     75   } {5 2 36 38 6 2 49 51 sort t1 i1w}
    66     76   do_test between-1.1.2 {
    67     77     queryplan {
    68     78       SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
    69     79     }
    70         -} {5 2 36 38 6 2 49 51 sort t1 {}}
           80  +} {5 2 36 38 6 2 49 51 sort t1 *}
    71     81   do_test between-1.2.1 {
    72     82     queryplan {
    73     83       SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
    74     84     }
    75     85   } {5 2 36 38 6 2 49 51 sort t1 i1w}
    76     86   do_test between-1.2.2 {
    77     87     queryplan {
    78     88       SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
    79     89     }
    80         -} {5 2 36 38 6 2 49 51 sort t1 {}}
           90  +} {5 2 36 38 6 2 49 51 sort t1 *}
    81     91   do_test between-1.3.1 {
    82     92     queryplan {
    83     93       SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
    84     94     }
    85     95   } {5 2 36 38 6 2 49 51 sort t1 i1w}
    86     96   do_test between-1.3.2 {
    87     97     queryplan {
    88     98       SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
    89     99     }
    90         -} {5 2 36 38 6 2 49 51 sort t1 {}}
          100  +} {5 2 36 38 6 2 49 51 sort t1 *}
    91    101   do_test between-1.4 {
    92    102     queryplan {
    93    103       SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
    94    104     }
    95         -} {5 2 36 38 6 2 49 51 sort t1 {}}
          105  +} {5 2 36 38 6 2 49 51 sort t1 *}
    96    106   do_test between-1.5.1 {
    97    107     queryplan {
    98    108       SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
    99    109     }
   100    110   } {4 2 25 27 sort t1 i1zyx}
   101    111   do_test between-1.5.2 {
   102    112     queryplan {
................................................................................
   103    113       SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
   104    114     }
   105    115   } {4 2 25 27 sort t1 i1zyx}
   106    116   do_test between-1.5.3 {
   107    117     queryplan {
   108    118       SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
   109    119     }
   110         -} {4 2 25 27 sort t1 {}}
          120  +} {4 2 25 27 sort t1 *}
   111    121   
   112    122   
   113    123   finish_test