SQLite4
Check-in [cc7bc86da5]
Check-in [cc7bc86da5]
Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Fix some problems to do with optimizing ORDER BY queries. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | nextgen-query-planner |
| Files: | files | file ages | folders |
| SHA1: |
cc7bc86da5d199db6383af7814152988 |
| User & Date: | dan 2013-07-19 18:33:48 |
|
2013-07-19
| ||
| 19:20 | Fix a bug to do with ORDER BY and collation sequences. check-in: 57b8ae848c user: dan tags: nextgen-query-planner | |
| 18:33 | Fix some problems to do with optimizing ORDER BY queries. check-in: cc7bc86da5 user: dan tags: nextgen-query-planner | |
|
2013-07-18
| ||
| 19:53 | Fix some problems with the LIKE optimization. check-in: 8330a46831 user: dan tags: nextgen-query-planner | |
Changes to src/where.c.
949 950 951 952 953 954 955 956 957 958 959 960 961 962 .... 5019 5020 5021 5022 5023 5024 5025 5026 5027 5028 5029 5030 5031 5032 5033 .... 5111 5112 5113 5114 5115 5116 5117 5118 5119 5120 5121 5122 5123 5124 5125 5126 5127 5128 5129 5130 5131 5132 5133 5134 5135 5136 5137 5138 5139 5140 .... 5145 5146 5147 5148 5149 5150 5151 5152 5153 5154 5155 5156 5157 5158 5159 5160 5161 5162 5163 .... 5183 5184 5185 5186 5187 5188 5189 5190 5191 5192 5193 5194 5195 5196 5197 5198 5199 5200 5201 5202 5203 5204 5205 5206 5207 5208 .... 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 5226 5227 5228 5229 5230 |
if( iIdxCol<pIdx->nColumn ){
zColl = pIdx->azColl[iIdxCol];
}else if( pPk && iIdxCol<(pIdx->nColumn + pPk->nColumn) ){
zColl = pPk->azColl[iIdxCol - pIdx->nColumn];
}
return zColl;
}
/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
return (pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn));
................................................................................
WhereLoop *pLast, /* Add this WhereLoop to the end of pPath->aLoop[] */
Bitmask *pRevMask /* OUT: Mask of WhereLoops to run in reverse order */
){
u8 revSet; /* True if rev is known */
u8 rev; /* Composite sort order */
u8 revIdx; /* Index sort order */
u8 isOrderDistinct; /* All prior WhereLoops are order-distinct */
u8 distinctColumns; /* True if the loop has UNIQUE NOT NULL columns */
u8 isMatch; /* iColumn matches a term of the ORDER BY clause */
u16 nColumn; /* Number of columns in pIndex */
u16 nOrderBy; /* Number terms in the ORDER BY clause */
int iLoop; /* Index of WhereLoop in pPath being processed */
int i, j; /* Loop counters */
int iCur; /* Cursor number for current WhereLoop */
int iColumn; /* A column number within table iCur */
................................................................................
z2 = pColl->zName;
if( sqlite4_stricmp(z1, z2)!=0 ) continue;
}
obSat |= MASKBIT(i);
}
if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
if( pLoop->wsFlags & WHERE_IPK ){
pIndex = 0;
nColumn = 0;
}else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
return 0;
}else{
nColumn = pIndex->nColumn;
isOrderDistinct = pIndex->onError!=OE_None;
}
/* Loop through all columns of the index and deal with the ones
** that are not constrained by == or IN.
*/
rev = revSet = 0;
distinctColumns = 0;
for(j=0; j<=nColumn; j++){
u8 bOnce; /* True to run the ORDER BY search loop */
/* Skip over == and IS NULL terms */
if( j<pLoop->u.btree.nEq
&& ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
){
if( i & WO_ISNULL ){
................................................................................
}
/* Get the column number in the table (iColumn) and sort order
** (revIdx) for the j-th column of the index.
*/
if( j<nColumn ){
/* Normal index columns */
iColumn = pIndex->aiColumn[j];
revIdx = pIndex->aSortOrder[j];
#if 0
if( iColumn==pIndex->pTable->iPKey ) iColumn = -1;
#endif
}else{
/* The ROWID column at the end */
assert( j==nColumn );
iColumn = -1;
revIdx = 0;
}
................................................................................
testcase( wctrlFlags & WHERE_GROUPBY );
testcase( wctrlFlags & WHERE_DISTINCTBY );
if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
if( pOBExpr->op!=TK_COLUMN ) continue;
if( pOBExpr->iTable!=iCur ) continue;
if( pOBExpr->iColumn!=iColumn ) continue;
if( iColumn>=0 ){
pColl = sqlite4ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
if( sqlite4_stricmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
}
isMatch = 1;
break;
}
if( isMatch ){
if( iColumn<0 ){
testcase( distinctColumns==0 );
distinctColumns = 1;
}
obSat |= MASKBIT(i);
if( (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){
/* Make sure the sort order is compatible in an ORDER BY clause.
** Sort order is irrelevant for a GROUP BY clause. */
if( revSet ){
if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) return 0;
}else{
................................................................................
if( j==0 || j<nColumn ){
testcase( isOrderDistinct!=0 );
isOrderDistinct = 0;
}
break;
}
} /* end Loop over all index columns */
if( distinctColumns ){
testcase( isOrderDistinct==0 );
isOrderDistinct = 1;
}
} /* end-if not one-row */
/* Mark off any other ORDER BY terms that reference pLoop */
if( isOrderDistinct ){
|
> > > > > > > > > > > > < > < > > < | | | < < < > > | < < < < < > > > > > > |
949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 .... 5031 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 .... 5122 5123 5124 5125 5126 5127 5128 5129 5130 5131 5132 5133 5134 5135 5136 5137 5138 5139 5140 5141 5142 5143 5144 5145 5146 5147 5148 5149 5150 5151 5152 .... 5157 5158 5159 5160 5161 5162 5163 5164 5165 5166 5167 5168 5169 5170 5171 5172 .... 5192 5193 5194 5195 5196 5197 5198 5199 5200 5201 5202 5203 5204 5205 5206 5207 5208 5209 5210 5211 5212 5213 5214 5215 .... 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241 5242 |
if( iIdxCol<pIdx->nColumn ){
zColl = pIdx->azColl[iIdxCol];
}else if( pPk && iIdxCol<(pIdx->nColumn + pPk->nColumn) ){
zColl = pPk->azColl[iIdxCol - pIdx->nColumn];
}
return zColl;
}
/*
** Return the sort order (SQLITE4_SO_ASC or DESC) used by the iIdxCol'th
** field in index pIdx, including any appended PRIMARY KEY fields.
*/
static int idxColumnSortOrder(Index *pIdx, Index *pPk, int iIdxCol){
int iRet = SQLITE4_SO_ASC;
if( iIdxCol<pIdx->nColumn ){
iRet = pIdx->aSortOrder[iIdxCol];
}
return iRet;
}
/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
return (pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn));
................................................................................
WhereLoop *pLast, /* Add this WhereLoop to the end of pPath->aLoop[] */
Bitmask *pRevMask /* OUT: Mask of WhereLoops to run in reverse order */
){
u8 revSet; /* True if rev is known */
u8 rev; /* Composite sort order */
u8 revIdx; /* Index sort order */
u8 isOrderDistinct; /* All prior WhereLoops are order-distinct */
u8 isMatch; /* iColumn matches a term of the ORDER BY clause */
u16 nColumn; /* Number of columns in pIndex */
u16 nOrderBy; /* Number terms in the ORDER BY clause */
int iLoop; /* Index of WhereLoop in pPath being processed */
int i, j; /* Loop counters */
int iCur; /* Cursor number for current WhereLoop */
int iColumn; /* A column number within table iCur */
................................................................................
z2 = pColl->zName;
if( sqlite4_stricmp(z1, z2)!=0 ) continue;
}
obSat |= MASKBIT(i);
}
if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
Index *pPk = 0;
if( pLoop->wsFlags & WHERE_IPK ){
pIndex = 0;
nColumn = 0;
}else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
return 0;
}else{
isOrderDistinct = pIndex->onError!=OE_None;
pPk = sqlite4FindPrimaryKey(pIndex->pTable, 0);
nColumn = idxColumnCount(pIndex, pPk);
}
/* Loop through all columns of the index and deal with the ones
** that are not constrained by == or IN.
*/
rev = revSet = 0;
for(j=0; j<nColumn; j++){
u8 bOnce; /* True to run the ORDER BY search loop */
/* Skip over == and IS NULL terms */
if( j<pLoop->u.btree.nEq
&& ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
){
if( i & WO_ISNULL ){
................................................................................
}
/* Get the column number in the table (iColumn) and sort order
** (revIdx) for the j-th column of the index.
*/
if( j<nColumn ){
/* Normal index columns */
iColumn = idxColumnNumber(pIndex, pPk, j);
revIdx = idxColumnSortOrder(pIndex, pPk, j);
}else{
/* The ROWID column at the end */
assert( j==nColumn );
iColumn = -1;
revIdx = 0;
}
................................................................................
testcase( wctrlFlags & WHERE_GROUPBY );
testcase( wctrlFlags & WHERE_DISTINCTBY );
if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
if( pOBExpr->op!=TK_COLUMN ) continue;
if( pOBExpr->iTable!=iCur ) continue;
if( pOBExpr->iColumn!=iColumn ) continue;
if( iColumn>=0 ){
const char *zIdxColl;
pColl = sqlite4ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
zIdxColl = idxColumnCollation(pPk, pIndex, j);
if( sqlite4_stricmp(pColl->zName, zIdxColl)!=0 ) continue;
}
isMatch = 1;
break;
}
if( isMatch ){
obSat |= MASKBIT(i);
if( (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){
/* Make sure the sort order is compatible in an ORDER BY clause.
** Sort order is irrelevant for a GROUP BY clause. */
if( revSet ){
if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) return 0;
}else{
................................................................................
if( j==0 || j<nColumn ){
testcase( isOrderDistinct!=0 );
isOrderDistinct = 0;
}
break;
}
} /* end Loop over all index columns */
/* If (j==nColumn), then each column of the index, including any
** appended PK columns, corresponds to either an ORDER BY term or
** equality constraint. Since the PK columns are collectively UNIQUE
** and NOT NULL, consider the loop order-distinct. */
if( j==nColumn ){
testcase( isOrderDistinct==0 );
isOrderDistinct = 1;
}
} /* end-if not one-row */
/* Mark off any other ORDER BY terms that reference pLoop */
if( isOrderDistinct ){
|
Changes to test/simple.test.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
....
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
|
#*********************************************************************** # The tests in this file were used while developing the SQLite 4 code. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix simple #set sqlite_where_trace 9 do_execsql_test 1.0 { PRAGMA table_info = sqlite_master } { 0 type text 0 {} 0 1 name text 0 {} 0 2 tbl_name text 0 {} 0 ................................................................................ do_execsql_test 86.3 { SELECT * FROM t1; } {1 one} do_execsql_test 86.4 { SELECT * FROM t1 WHERE a = 1; } {1 one} finish_test |
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
|
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
|
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple
do_execsql_test 1.0 {
PRAGMA table_info = sqlite_master
} {
0 type text 0 {} 0
1 name text 0 {} 0
2 tbl_name text 0 {} 0
................................................................................
do_execsql_test 86.3 {
SELECT * FROM t1;
} {1 one}
do_execsql_test 86.4 {
SELECT * FROM t1 WHERE a = 1;
} {1 one}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 87.1 {
CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
CREATE INDEX t6i1 ON t6(b);
} {}
do_eqp_test 87.2 {
SELECT * FROM t6 ORDER BY b, a;
} {0 0 0 {SCAN TABLE t6 USING INDEX t6i1}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 88.1 {
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT);
CREATE UNIQUE INDEX t8i ON t8(b);
}
do_eqp_test 88.2 {
SELECT * FROM t8 x ORDER BY x.b, x.a, x.b||x.a
} {0 0 0 {SCAN TABLE t8 AS x USING INDEX t8i}}
finish_test
|
Changes to test/tester.tcl.
491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 |
if {![info exists ::G(match)] || [string match $::G(match) $name]} {
if {[catch {uplevel #0 "$cmd;\n"} result]} {
puts "\nError: $result"
fail_test $name
} else {
if {[regexp {^~?/.*/$} $expected]} {
if {[string index $expected 0]=="~"} {
set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]]
set ok [expr {![regexp $re $result]}]
} else {
set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]]
set ok [regexp $re $result]
}
} else {
set ok [expr {[string compare $result $expected]==0}]
}
if {!$ok} {
# if {![info exists ::testprefix] || $::testprefix eq ""} {
# error "no test prefix"
|
> > > > > > > > > > > > > |
491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 |
if {![info exists ::G(match)] || [string match $::G(match) $name]} {
if {[catch {uplevel #0 "$cmd;\n"} result]} {
puts "\nError: $result"
fail_test $name
} else {
if {[regexp {^~?/.*/$} $expected]} {
# "expected" is of the form "/PATTERN/" then the result if correct if
# regular expression PATTERN matches the result. "~/PATTERN/" means
# the regular expression must not match.
if {[string index $expected 0]=="~"} {
set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]]
set ok [expr {![regexp $re $result]}]
} else {
set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]]
set ok [regexp $re $result]
}
} elseif {[regexp {^~?\*.*\*$} $expected]} {
# "expected" is of the form "*GLOB*" then the result if correct if
# glob pattern GLOB matches the result. "~/GLOB/" means
# the glob must not match.
if {[string index $expected 0]=="~"} {
set e [string range $expected 1 end]
set ok [expr {![string match $e $result]}]
} else {
set ok [string match $expected $result]
}
} else {
set ok [expr {[string compare $result $expected]==0}]
}
if {!$ok} {
# if {![info exists ::testprefix] || $::testprefix eq ""} {
# error "no test prefix"
|
Changes to test/where.test.
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 ... 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 152 153 154 155 156 157 158 159 ... 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 .... 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 |
proc count sql {
kvwrap reset
set res [execsql $sql]
#puts "sql={$sql} seek=[kvwrap seek] step=[kvwrap step]"
return [concat $res [expr [kvwrap step] + [kvwrap seek]]]
}
# Verify that queries use an index. We are using the special variable
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE. By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1.1 {
count {SELECT x, y, w FROM t1 WHERE w=10}
} {3 121 10 3}
do_test where-1.1.2 {
set sqlite_query_plan
} {t1 i1w}
do_test where-1.1.3 {
db status step
} {0}
do_test where-1.1.4 {
db eval {SELECT x, y, w FROM t1 WHERE +w=10}
} {3 121 10}
do_test where-1.1.5 {
db status step
} {99}
do_test where-1.1.6 {
set sqlite_query_plan
} {t1 {}}
do_test where-1.1.7 {
count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
} {3 121 10 3}
do_test where-1.1.8 {
set sqlite_query_plan
} {t1 i1w}
do_test where-1.1.9 {
db status step
} {0}
do_test where-1.2.1 {
count {SELECT x, y, w FROM t1 WHERE w=11}
} {3 144 11 3}
do_test where-1.2.2 {
................................................................................
} {3 144 11 3}
do_test where-1.3.2 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
} {3 144 11 3}
do_test where-1.4.1 {
count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
} {11 3 144 3}
do_test where-1.4.2 {
set sqlite_query_plan
} {t1 i1w}
do_test where-1.4.3 {
count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
} {11 3 144 3}
do_test where-1.4.4 {
set sqlite_query_plan
} {t1 i1w}
do_test where-1.5 {
count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.5.2 {
set sqlite_query_plan
} {t1 i1w}
do_test where-1.6 {
count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test where-1.7 {
count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test where-1.8 {
count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_test where-1.8.2 {
set sqlite_query_plan
} {t1 i1xy}
do_test where-1.8.3 {
count {SELECT x, y FROM t1 WHERE y=144 AND x=3}
set sqlite_query_plan
} {t1 i1xy}
do_test where-1.9 {
count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test where-1.10 {
count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {
count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}
# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test where-1.12 {
count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
................................................................................
do_test where-6.6 {
cksort {
SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
}
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.7 {
# UPDATE: src4 does a sort here. It picks a different index because it
# does not support the covering index optimization.
cksort {
SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
}
} {1 100 4 2 99 9 3 98 16 sort}
ifcapable subquery {
do_test where-6.8 {
cksort {
SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
}
} {1 100 4 2 99 9 3 98 16 sort}
}
do_test where-6.9.1 {
cksort {
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
}
} {1 100 4 nosort}
do_test where-6.9.1.1 {
................................................................................
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
INSERT INTO t8 VALUES(1,'one');
INSERT INTO t8 VALUES(4,'four');
}
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
}
} {1/4 1/1 4/4 4/1 sort}
do_test where-14.2 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
}
} {1/1 1/4 4/1 4/4 sort}
do_test where-14.3 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
}
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.4 {
cksort {
|
| < < | > | | | | | | | | | | | | | | | | | | | | | | | | < > < < | | | | |
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 ... 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 152 153 154 155 156 157 158 ... 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 .... 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 |
proc count sql {
kvwrap reset
set res [execsql $sql]
#puts "sql={$sql} seek=[kvwrap seek] step=[kvwrap step]"
return [concat $res [expr [kvwrap step] + [kvwrap seek]]]
}
# Verify that queries use an index. By verifing that the KVWrap layer
# xNext/xPrev/xSeek count is small we can be assured that indices are
# being used properly.
#
do_test where-1.1.1 {
count {SELECT x, y, w FROM t1 WHERE w=10}
} {3 121 10 3}
do_eqp_test where-1.1.2 {
SELECT x, y, w FROM t1 WHERE w=10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.1.3 {
db status step
} {0}
do_test where-1.1.4 {
db eval {SELECT x, y, w FROM t1 WHERE +w=10}
} {3 121 10}
do_test where-1.1.5 {
db status step
} {99}
do_eqp_test where-1.1.6 {
SELECT x, y, w FROM t1 WHERE +w=10
} {*SCAN TABLE t1*}
do_test where-1.1.7 {
count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
} {3 121 10 3}
do_eqp_test where-1.1.8 {
SELECT x, y, w AS abc FROM t1 WHERE abc=10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.1.9 {
db status step
} {0}
do_test where-1.2.1 {
count {SELECT x, y, w FROM t1 WHERE w=11}
} {3 144 11 3}
do_test where-1.2.2 {
................................................................................
} {3 144 11 3}
do_test where-1.3.2 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
} {3 144 11 3}
do_test where-1.4.1 {
count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
} {11 3 144 3}
do_eqp_test where-1.4.2 {
SELECT w, x, y FROM t1 WHERE 11=w AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.4.3 {
count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
} {11 3 144 3}
do_eqp_test where-1.4.4 {
SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.5 {
count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_eqp_test where-1.5.2 {
SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.6 {
count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test where-1.7 {
count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test where-1.8 {
count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_eqp_test where-1.8.2 {
SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
do_eqp_test where-1.8.3 {
SELECT x, y FROM t1 WHERE y=144 AND x=3
} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
do_test where-1.9 {
count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test where-1.10 {
count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {
count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}
# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test where-1.12 {
count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
................................................................................
do_test where-6.6 {
cksort {
SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
}
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.7 {
cksort {
SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
}
} {1 100 4 2 99 9 3 98 16 nosort}
ifcapable subquery {
do_test where-6.8 {
cksort {
SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
}
} {1 100 4 2 99 9 3 98 16 nosort}
}
do_test where-6.9.1 {
cksort {
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
}
} {1 100 4 nosort}
do_test where-6.9.1.1 {
................................................................................
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
INSERT INTO t8 VALUES(1,'one');
INSERT INTO t8 VALUES(4,'four');
}
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
}
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.2 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
}
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.3 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
}
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.4 {
cksort {
|