Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix where.c so that the primary key values appended to every index entry on a WITHOUT ROWID table may be used when useful. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | stat4-without-rowid |
| Files: | files | file ages | folders |
| SHA1: |
6624a61d16e47ad691c4195ca8a1d68b |
| User & Date: | dan 2014-06-30 15:23:57 |
Context
|
2014-06-30
| ||
| 17:07 | Fix the STAT4 information for WITHOUT ROWID tables. check-in: 5d8628fd user: drh tags: stat4-without-rowid | |
| 15:23 | Fix where.c so that the primary key values appended to every index entry on a WITHOUT ROWID table may be used when useful. check-in: 6624a61d user: dan tags: stat4-without-rowid | |
| 13:56 | Omit non-primary-key columns from the STAT4 samples for WITHOUT ROWID tables. Indexes, both rowid and without-rowid, still hold an exact copy of the index entry. check-in: de826c31 user: drh tags: stat4-without-rowid | |
Changes
Changes to src/where.c.
540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 .... 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 .... 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 .... 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 .... 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 4514 4515 |
/* memset(pScan, 0, sizeof(*pScan)); */
pScan->pOrigWC = pWC;
pScan->pWC = pWC;
if( pIdx && iColumn>=0 ){
pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
if( NEVER(j>=pIdx->nKeyCol) ) return 0;
}
pScan->zCollName = pIdx->azColl[j];
}else{
pScan->idxaff = 0;
pScan->zCollName = 0;
}
pScan->opMask = opMask;
................................................................................
if( nEq==0 && (pLoop->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){
return 0;
}
sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH);
txt.db = db;
sqlite3StrAccumAppend(&txt, " (", 2);
for(i=0; i<nEq; i++){
char *z = (i==pIndex->nKeyCol ) ? "rowid" : aCol[aiColumn[i]].zName;
if( i>=nSkip ){
explainAppendTerm(&txt, i, z, "=");
}else{
if( i ) sqlite3StrAccumAppend(&txt, " AND ", 5);
sqlite3StrAccumAppend(&txt, "ANY(", 4);
sqlite3StrAccumAppendAll(&txt, z);
sqlite3StrAccumAppend(&txt, ")", 1);
}
}
j = i;
if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
char *z = (j==pIndex->nKeyCol ) ? "rowid" : aCol[aiColumn[j]].zName;
explainAppendTerm(&txt, i++, z, ">");
}
if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
char *z = (j==pIndex->nKeyCol ) ? "rowid" : aCol[aiColumn[j]].zName;
explainAppendTerm(&txt, i, z, "<");
}
sqlite3StrAccumAppend(&txt, ")", 1);
return sqlite3StrAccumFinish(&txt);
}
/*
................................................................................
}else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
}else{
opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
}
if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
assert( pNew->u.btree.nEq<=pProbe->nKeyCol );
if( pNew->u.btree.nEq < pProbe->nKeyCol ){
iCol = pProbe->aiColumn[pNew->u.btree.nEq];
}else{
iCol = -1;
}
pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
opMask, pProbe);
saved_nEq = pNew->u.btree.nEq;
saved_nSkip = pNew->u.btree.nSkip;
saved_nLTerm = pNew->nLTerm;
saved_wsFlags = pNew->wsFlags;
saved_prereq = pNew->prereq;
................................................................................
if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
pNew->nOut = saved_nOut;
}else{
pNew->nOut = nOutUnadjusted;
}
if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
&& pNew->u.btree.nEq<(pProbe->nKeyCol + (pProbe->zName!=0))
){
whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
}
pNew->nOut = saved_nOut;
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
pBuilder->nRecValid = nRecValid;
#endif
................................................................................
/* There is no INDEXED BY clause. Create a fake Index object in local
** variable sPk to represent the rowid primary key index. Make this
** fake index the first in a chain of Index objects with all of the real
** indices to follow */
Index *pFirst; /* First of real indices on the table */
memset(&sPk, 0, sizeof(Index));
sPk.nKeyCol = 1;
sPk.aiColumn = &aiColumnPk;
sPk.aiRowLogEst = aiRowEstPk;
sPk.onError = OE_Replace;
sPk.pTable = pTab;
sPk.szIdxRow = pTab->szTabRow;
aiRowEstPk[0] = pTab->nRowLogEst;
aiRowEstPk[1] = 0;
|
| | | | | < | < < | | > |
540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 .... 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 .... 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 .... 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 .... 4499 4500 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 |
/* memset(pScan, 0, sizeof(*pScan)); */
pScan->pOrigWC = pWC;
pScan->pWC = pWC;
if( pIdx && iColumn>=0 ){
pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
if( NEVER(j>pIdx->nColumn) ) return 0;
}
pScan->zCollName = pIdx->azColl[j];
}else{
pScan->idxaff = 0;
pScan->zCollName = 0;
}
pScan->opMask = opMask;
................................................................................
if( nEq==0 && (pLoop->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){
return 0;
}
sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH);
txt.db = db;
sqlite3StrAccumAppend(&txt, " (", 2);
for(i=0; i<nEq; i++){
char *z = aiColumn[i] < 0 ? "rowid" : aCol[aiColumn[i]].zName;
if( i>=nSkip ){
explainAppendTerm(&txt, i, z, "=");
}else{
if( i ) sqlite3StrAccumAppend(&txt, " AND ", 5);
sqlite3StrAccumAppend(&txt, "ANY(", 4);
sqlite3StrAccumAppendAll(&txt, z);
sqlite3StrAccumAppend(&txt, ")", 1);
}
}
j = i;
if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
explainAppendTerm(&txt, i++, z, ">");
}
if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
explainAppendTerm(&txt, i, z, "<");
}
sqlite3StrAccumAppend(&txt, ")", 1);
return sqlite3StrAccumFinish(&txt);
}
/*
................................................................................
}else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
}else{
opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
}
if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
assert( pNew->u.btree.nEq<pProbe->nColumn );
iCol = pProbe->aiColumn[pNew->u.btree.nEq];
pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
opMask, pProbe);
saved_nEq = pNew->u.btree.nEq;
saved_nSkip = pNew->u.btree.nSkip;
saved_nLTerm = pNew->nLTerm;
saved_wsFlags = pNew->wsFlags;
saved_prereq = pNew->prereq;
................................................................................
if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
pNew->nOut = saved_nOut;
}else{
pNew->nOut = nOutUnadjusted;
}
if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
&& pNew->u.btree.nEq<pProbe->nColumn
){
whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
}
pNew->nOut = saved_nOut;
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
pBuilder->nRecValid = nRecValid;
#endif
................................................................................
/* There is no INDEXED BY clause. Create a fake Index object in local
** variable sPk to represent the rowid primary key index. Make this
** fake index the first in a chain of Index objects with all of the real
** indices to follow */
Index *pFirst; /* First of real indices on the table */
memset(&sPk, 0, sizeof(Index));
sPk.nKeyCol = 1;
sPk.nColumn = 1;
sPk.aiColumn = &aiColumnPk;
sPk.aiRowLogEst = aiRowEstPk;
sPk.onError = OE_Replace;
sPk.pTable = pTab;
sPk.szIdxRow = pTab->szTabRow;
aiRowEstPk[0] = pTab->nRowLogEst;
aiRowEstPk[1] = 0;
|
Changes to test/without_rowid1.test.
209 210 211 212 213 214 215 216 217 |
CREATE TABLE t42(x);
INSERT INTO t42 VALUES('xyz');
SELECT t42.rowid FROM t41, t42;
} {1}
do_execsql_test 4.2 {
SELECT t42.rowid FROM t42, t41;
} {1}
finish_test
|
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
209 210 211 212 213 214 215 216 217 218 219 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 272 273 274 275 276 277 278 279 280 281 |
CREATE TABLE t42(x);
INSERT INTO t42 VALUES('xyz');
SELECT t42.rowid FROM t41, t42;
} {1}
do_execsql_test 4.2 {
SELECT t42.rowid FROM t42, t41;
} {1}
#--------------------------------------------------------------------------
# The following tests verify that the trailing PK fields added to each
# entry in an index on a WITHOUT ROWID table are used correctly.
#
do_execsql_test 5.0 {
CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID;
CREATE INDEX i45 ON t45(b);
INSERT INTO t45 VALUES(2, 'one', 'x');
INSERT INTO t45 VALUES(4, 'one', 'x');
INSERT INTO t45 VALUES(6, 'one', 'x');
INSERT INTO t45 VALUES(8, 'one', 'x');
INSERT INTO t45 VALUES(10, 'one', 'x');
INSERT INTO t45 VALUES(1, 'two', 'x');
INSERT INTO t45 VALUES(3, 'two', 'x');
INSERT INTO t45 VALUES(5, 'two', 'x');
INSERT INTO t45 VALUES(7, 'two', 'x');
INSERT INTO t45 VALUES(9, 'two', 'x');
}
do_eqp_test 5.1 {
SELECT * FROM t45 WHERE b=? AND a>?
} {/*USING INDEX i45 (b=? AND a>?)*/}
do_execsql_test 5.2 {
SELECT * FROM t45 WHERE b='two' AND a>4
} {5 two x 7 two x 9 two x}
do_execsql_test 5.3 {
SELECT * FROM t45 WHERE b='one' AND a<8
} { 2 one x 4 one x 6 one x }
do_execsql_test 5.4 {
CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
WITH r(x) AS (
SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100
)
INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r;
}
set queries {
1 2 "c = 5 AND a = 1" {/*i46 (c=? AND a=?)*/}
2 6 "c = 4 AND a < 3" {/*i46 (c=? AND a<?)*/}
3 4 "c = 2 AND a >= 3" {/*i46 (c=? AND a>?)*/}
4 1 "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/}
5 1 "c = 0 AND a = 0 AND b>5" {/*i46 (c=? AND a=? AND b>?)*/}
}
foreach {tn cnt where eqp} $queries {
do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
}
do_execsql_test 5.6 {
CREATE INDEX i46 ON t46(c);
}
foreach {tn cnt where eqp} $queries {
do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
do_eqp_test 5.7.$tn.2 "SELECT count(*) FROM t46 WHERE $where" $eqp
}
finish_test
|