Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with using stat4 data to estimate the number of rows scanned by a range constraint on the second or subsequent column of any index where an affinity transformation must be applied to the constraint argument. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
c21f58d84859e479a6cc619671a0df48 |
| User & Date: | dan 2013-09-02 07:16:40 |
Context
|
2013-09-02
| ||
| 11:52 | Add tests to check if ANALYZE is choosing common non-periodic samples for the stat4 table. check-in: 17584299 user: dan tags: trunk | |
| 07:16 | Fix a problem with using stat4 data to estimate the number of rows scanned by a range constraint on the second or subsequent column of any index where an affinity transformation must be applied to the constraint argument. check-in: c21f58d8 user: dan tags: trunk | |
|
2013-08-31
| ||
| 18:36 | Cleanup of the windows VFS, including added support for Cygwin, fixes for compiler warnings under unusual configurations, and improved diagnostic error messages. check-in: c94933f1 user: drh tags: trunk | |
Changes
Changes to src/where.c.
2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 |
if( nEq==pBuilder->nRecValid
&& nEq<p->nSampleCol
&& p->nSample
&& OptimizationEnabled(pParse->db, SQLITE_Stat3)
){
UnpackedRecord *pRec = pBuilder->pRec;
tRowcnt a[2];
u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
/* Variable iLower will be set to the estimate of the number of rows in
** the index that are less than the lower bound of the range query. The
** lower bound being the concatenation of $P and $L, where $P is the
** key-prefix formed by the nEq values matched against the nEq left-most
** columns of the index, and $L is the value in pLower.
**
|
> > > > | > |
2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 |
if( nEq==pBuilder->nRecValid
&& nEq<p->nSampleCol
&& p->nSample
&& OptimizationEnabled(pParse->db, SQLITE_Stat3)
){
UnpackedRecord *pRec = pBuilder->pRec;
tRowcnt a[2];
u8 aff;
if( nEq==p->nColumn ){
aff = SQLITE_AFF_INTEGER;
}else{
aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
}
/* Variable iLower will be set to the estimate of the number of rows in
** the index that are less than the lower bound of the range query. The
** lower bound being the concatenation of $P and $L, where $P is the
** key-prefix formed by the nEq values matched against the nEq left-most
** columns of the index, and $L is the value in pLower.
**
|
Changes to test/analyze9.test.
551 552 553 554 555 556 557 558 559 560 561 |
do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
set sql {
SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
}
do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
}
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 |
do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
set sql {
SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
}
do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
}
}
#-------------------------------------------------------------------------
# Check that affinities are taken into account when using stat4 data to
# estimate the number of rows scanned by a rowid constraint.
#
drop_all_tables
do_test 13.1 {
execsql {
CREATE TABLE t1(a, b, c);
CREATE INDEX i1 ON t1(a);
CREATE INDEX i2 ON t1(b, c);
}
for {set i 0} {$i<100} {incr i} {
if {$i %2} {set a abc} else {set a def}
execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) }
}
execsql ANALYZE
} {}
do_eqp_test 13.2.1 {
SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.2.2 {
SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.3.1 {
SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20
} {/SEARCH TABLE t1 USING INDEX i2/}
do_eqp_test 13.3.2 {
SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20
} {/SEARCH TABLE t1 USING INDEX i2/}
#-------------------------------------------------------------------------
# Check also that affinities are taken into account when using stat4 data
# to estimate the number of rows scanned by any other constraint on a
# column other than the leftmost.
#
drop_all_tables
do_test 14.1 {
execsql { CREATE TABLE t1(a, b INTEGER, c) }
for {set i 0} {$i<100} {incr i} {
set c [expr $i % 3]
execsql { INSERT INTO t1 VALUES('ott', $i, $c) }
}
execsql {
CREATE INDEX i1 ON t1(a, b);
CREATE INDEX i2 ON t1(c);
ANALYZE;
}
} {}
do_eqp_test 13.2.1 {
SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.2.2 {
SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1
} {/SEARCH TABLE t1 USING INDEX i1/}
finish_test
|