SQLite

Check-in [dfb09db6d4]
Login

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

Overview
Comment:Fix a problem with using stat4 samples of type text when estimating the rows visited by a range-query/skip-scan loop.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | stat4-skipscan
Files: files | file ages | folders
SHA1: dfb09db6d412f3bc2a71bda393813783580dbad1
User & Date: dan 2014-06-28 15:26:10.708
Context
2014-06-28
16:06
Add header comments on new routines. Rework the sqlite3Stat4Column() routine so that is (in theory) able to deal with corrupt samples. (check-in: ef5cdf949b user: drh tags: stat4-skipscan)
15:26
Fix a problem with using stat4 samples of type text when estimating the rows visited by a range-query/skip-scan loop. (check-in: dfb09db6d4 user: dan tags: stat4-skipscan)
14:28
Change the VDBE to export the sqlite3MemCompare() routine and thus free where.c from the dependency on vdbeInt.h. (check-in: d186d1ac3c user: drh tags: stat4-skipscan)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbemem.c.
︙︙
1297
1298
1299
1300
1301
1302
1303

1304
1305
1306
1307
1308
1309
1310

    iField = nHdr;
    for(i=0; i<iCol; i++){
      iHdr += getVarint32(&a[iHdr], t);
      iField += sqlite3VdbeSerialTypeLen(t);
    }

    iHdr = getVarint32(&a[iHdr], t);

    sqlite3VdbeSerialGet(&a[iField], t, pMem);
  }

  *ppVal = pMem;
  return rc;
}









>








1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311

    iField = nHdr;
    for(i=0; i<iCol; i++){
      iHdr += getVarint32(&a[iHdr], t);
      iField += sqlite3VdbeSerialTypeLen(t);
    }

    iHdr = getVarint32(&a[iHdr], t);
    pMem->enc = ENC(db);
    sqlite3VdbeSerialGet(&a[iField], t, pMem);
  }

  *ppVal = pMem;
  return rc;
}
︙︙
Changes to src/where.c.
︙︙
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101

    ** the number of rows visited. Otherwise, estimate the number of rows
    ** using the method described in the header comment for this function. */
    if( nDiff!=1 || pUpper==0 || pLower==0 ){
      int nAdjust = (sqlite3LogEst(p->nSample) - sqlite3LogEst(nDiff));
      pLoop->nOut -= nAdjust;
      *pbDone = 1;
      WHERETRACE(0x10, ("range skip-scan regions: %u..%u  adjust=%d est=%d\n",
                           (u32)nLower, (u32)nUpper, nAdjust*-1, pLoop->nOut));
    }

  }else{
    assert( *pbDone==0 );
  }

  sqlite3ValueFree(p1);








|








2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101

    ** the number of rows visited. Otherwise, estimate the number of rows
    ** using the method described in the header comment for this function. */
    if( nDiff!=1 || pUpper==0 || pLower==0 ){
      int nAdjust = (sqlite3LogEst(p->nSample) - sqlite3LogEst(nDiff));
      pLoop->nOut -= nAdjust;
      *pbDone = 1;
      WHERETRACE(0x10, ("range skip-scan regions: %u..%u  adjust=%d est=%d\n",
                           nLower, nUpper, nAdjust*-1, pLoop->nOut));
    }

  }else{
    assert( *pbDone==0 );
  }

  sqlite3ValueFree(p1);
︙︙
Changes to test/skipscan5.test.
︙︙
87
88
89
90
91
92
93
94




















































95





}

do_eqp_test 1.11 {
  SELECT * FROM t1 WHERE b < 15;
} {
  0 0 0 {SCAN TABLE t1}
}





















































finish_test













>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>

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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

}

do_eqp_test 1.11 {
  SELECT * FROM t1 WHERE b < 15;
} {
  0 0 0 {SCAN TABLE t1}
}

#-------------------------------------------------------------------------
# Test that range-query/skip-scan estimation works with text values.
# And on UTF-16 databases when there is no UTF-16 collation sequence
# available.

proc test_collate {enc lhs rhs} {
  string compare $lhs $rhs
}

foreach {tn dbenc coll} {
  1 UTF-8   { add_test_collate db 0 0 1 }
  2 UTF-16  { add_test_collate db 1 0 0 }
  3 UTF-8   { add_test_collate db 0 1 0 }
} {
  reset_db
  eval $coll

  do_execsql_test 2.$tn.1 " PRAGMA encoding = '$dbenc' "
  do_execsql_test 2.$tn.2 {
    CREATE TABLE t2(a TEXT, b TEXT, c TEXT COLLATE test_collate, d TEXT);
    CREATE INDEX i2 ON t2(a, b, c);
  }

  set vocab(d) { :) }
  set vocab(c) { a b c d e f g h i j k l m n o p q r s t }
  set vocab(b) { one two three }
  set vocab(a) { sql }

  do_test 2.$tn.3 {
    for {set i 0} {$i < 100} {incr i} {
      foreach var {a b c d} { 
        set $var [lindex $vocab($var) [expr $i % [llength $vocab($var)]]]
      }
      execsql { INSERT INTO t2 VALUES($a, $b, $c, $d) }
    }
    execsql ANALYZE
  } {}

  foreach {tn2 q res} {
    1 { c BETWEEN 'd' AND 'e' }       {/*ANY(a) AND ANY(b) AND c>? AND c<?*/}
    2 { c BETWEEN 'b' AND 'r' }       {/*SCAN TABLE t2*/}
    3 { c > 'q' }                     {/*ANY(a) AND ANY(b) AND c>?*/}
    4 { c > 'e' }                     {/*SCAN TABLE t2*/}
    5 { c < 'q' }                     {/*SCAN TABLE t2*/}
    4 { c < 'e' }                     {/*ANY(a) AND ANY(b) AND c<?*/}
  } {
    set sql "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE $q" 
    do_execsql_test 2.$tn.$tn2 $sql $res
  }

}

finish_test